sqlsql-serverdynamic-sqlcontext-info

bad string in dynamic sql and context info


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!


Solution

  • 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().