Sql-server – Special letter behaviour problem

castdynamic-sqlsql-server-2012unicodexml

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.

Best Answer

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
------      --------
 ?          ??

Related Solutions

Sql-server – Select statement to retrieve xml output as below

Give this a try:

SELECT 
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    t2.Phone AS "Personal/Phone",
    t2.City AS "Personal/City"
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

I have added a second code sample in response to your comment. I have no idea if this will work, or if it is even the best way to accomplish what you want it to. Please let me know.

SELECT 
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    (
        SELECT
            t2.Phone AS "Personal/Phone",
            t2.City AS "Personal/City"
        FROM table1 t2

            INNER JOIN table2 t3
                ON t2.rid = t3.rid

        WHERE t2.rid = t1.rid

        FOR XML PATH('Personals')
    )
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

Sql-server – SQL Server stored procedure working when run manually, not running from SQL Server Agent

line 1, character 38, unable to switch the encoding

I ran in to this a while back sql server wants the source string type to match the utf code.

    SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO

You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.

line 9, character 9, unexpected end of input

I would run this problem from a different direction.

have the procedure output the received xml
check for permissions issues with the service accounts (difference between sql server and agent services)

Best Answer

Related Solutions

Sql-server – Select statement to retrieve xml output as below

Sql-server – SQL Server stored procedure working when run manually, not running from SQL Server Agent

Related Question