Storing and retrieving strings in the SQL Server session context

Sometimes it can be useful to store temporary data (e.g. a string with the current username for auditing or similar) in the context of the current Sql Server session and it’s actually rather easy to do. All that’s needed is to convert the data to a varbinary of max length 128 and then call SET CONTEXT_INFO like this:

-- store data
DECLARE @encodedUsername AS VARBINARY(128)
SET @encodedUsername = convert(varbinary(128),'emila')
SET CONTEXT_INFO @encodedUsername

Unfortunately it’s not quite as easy to retrieve the original string… You can retrieve the varbinary data using CONTEXT_INFO(), but that will only give the varbinary data back:

SELECT CONTEXT_INFO()

0x656D696C610000000000000...

Converting that data back to a varchar seems to work at first but the string contains trailing 0-characters (cf. CHAR(0) ):

-- Convert to a string, looks ok...
SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO())
emila

-- ... but the length is 128 characters
SELECT LEN(CONVERT(VARCHAR(MAX), CONTEXT_INFO()))
128

Removing those illegal character is surprisingly difficult (e.g. REPLACE() cannot be used) but one possible solution is to do a conversion into an Xml string:

-- convert to xml for 
SELECT CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
<data>emila&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;...

Substrings of that Xml string can then be replaced like in any other string and that logic is best put in a user-defined function:

-- Retrieve data from the context and cleanup illegal characters
CREATE FUNCTION GetLoggedInUserName() RETURNS varchar(20)
AS
  BEGIN
    RETURN
      REPLACE(
        REPLACE(
          REPLACE(
            CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
            , '</data><data>', '')
          , '</data>', '')
        , '&#x00;', '')
  END
GO


SELECT dbo.GetLoggedInUserName()
emila

The result is now a string of the correct length and without illegal characters.

This code works as expected, but perhaps there is a more efficient alternative out there? Please leave a comment if you have a suggestion for improvement 🙂

/Emil