bad string in dynamic sql and context info

Question

This is so strange! The following code is a t-sql.

BEGIN
    DECLARE @cinfo VARBINARY(128) = CAST('aud:83/53784862/1' AS VARBINARY(128));
    DECLARE @csinfo VARCHAR(128) = CAST(CONTEXT_INFO() AS VARCHAR(128));
    SET @csinfo = '<root><value>' + REPLACE(RIGHT(@cinfo, LEN(@cinfo) - 4), '/', '</value><value>') + '</value></root>';
    PRINT @csinfo
    SET CONTEXT_INFO @cinfo;
    DECLARE @sql VARCHAR(MAX) = '
        DECLARE @cinfo VARCHAR(128) = CAST(CONTEXT_INFO() AS VARCHAR(128));
        DECLARE @csinfo VARCHAR(512) = ''<root><value>'' + REPLACE(RIGHT(@cinfo, LEN(@cinfo) - 4), ''/'', ''</value><value>'') + ''</value></root>'';
        PRINT @csinfo
    '
    EXEC(@sql)
END

This is the output:

<root><value>83</value><value>53784862</value><value>1</value></root>
<root><value>83</value><value>53784862</value><value>1

Why </value></root> is not appended!

its appended.. Just with some spaces. Observe in the right corner side of SSMS — Shakeer Mirza, Jul 20 '18 at 12:00
@ShakeerMirza no, its not appended. because the sql server said the latter string is not a valid xml. — vipcxj, Jul 20 '18 at 12:02
@Larnu you mean there is a null character at the end of the latter string, so no string can be appended to it? — vipcxj, Jul 20 '18 at 12:03
A NULL character (`CHAR(0)`), and a string of the value `NULL` are different. — Thom A, Jul 20 '18 at 12:06
@Larnu I know the meaning of null character. The current problem is that no string can be appended to `REPLACE(RIGHT(@cinfo, LEN(@cinfo) - 4), ''/'', '''')` in the dynamic sql. But the same code works in the static environment. — vipcxj, Jul 20 '18 at 15:13
What do you mean a "static" environment? If you run that SQL outside of dynamic SQL you get the same result (with a load a NULL characters at the end). — Thom A, Jul 20 '18 at 15:35
just see the question, the first output is out of dynamic sql, and this is what I want. — vipcxj, Jul 20 '18 at 17:28
The output from the dynamic SQL is no different to if you run it non-dynamically. They both have a load of NULL characters before the end tag. This is not a surprise, and the value of `CONTEXT_INFO` ends with a lot of 0's, which would convert into a NULL character. — Thom A, Jul 20 '18 at 21:09

score 0 · Accepted Answer · answered Jul 24 '18 at 03:49

0

I found the solution.

DECLARE @cinfo VARCHAR(128) = REPLACE(CAST(CAST(CONTEXT_INFO() AS VARCHAR(128)) COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(128)), CHAR(0), '');

this will repair the string convert from CONTEXT_INFO().

answered Jul 24 '18 at 03:49

vipcxj

840
5
10

bad string in dynamic sql and context info

1 Answers1