0

I asked question in very blurry manner because I cannot explain in short sentence what is my problem about.

In general I have a set of procedures which transforms text and XML. Inside the procedures I have a lot of NVARCHAR and XML variables and a table with XML column. Inside my data I have special letters (for example ò). When I run all the code the special character disappears and is visible as ? - which mean that I got VARCHAR instead of NVARCHAR.

In general I got everything ok until the final steps. I got EXEC which call stored procedure with ad hoc constructed variable of type NVARCHAR. I have into a table which contains XML field. In this case I got ?. If I run procedure manually (not in EXEC INTO) I got proper symbol.

Any ideas why this happens? I checked all variables and they are NVARCHAR.

  • 2
    Please, until you set up a verifiable example, this is not clear or too localized – McNets Dec 1 '17 at 13:45
  • 1
    Have you seen this question? stackoverflow.com/questions/16816824/… – Jonathan Fite Dec 1 '17 at 13:46
  • @JonathanFite, 10x for link. It seems something like my case. I just need to try the solution. – Bogdan Bogdanov Dec 1 '17 at 14:10
  • You checked that all your variables are nvarchar but when you use single quotes to delimit the strings, do you always prefix them with N? Compare: SELECT N'💩', '💩'; – Aaron Bertrand Dec 1 '17 at 14:35
  • Yes, @AaronBertrand. This was fault actually. I did forgot to set N before my string when calling procedure. I did not check that. – Bogdan Bogdanov Dec 1 '17 at 14:55
6

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string with 'single quotes', you need to prefix them with an N''. N stands for national, not nvarchar (admittedly, a U prefix - for Unicode - would have made more sense).

Compare:

SELECT Prefix = N'💩', NoPrefix = '💩';

Results:

Prefix      NoPrefix
------      --------
 💩          ??
  • 1
    var day = made; – Nelz Dec 1 '17 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.