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�������...
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>', '') , '�', '') 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 🙂