In an attempt to build example code for this question, I ran into a problem with CONTEXT_INFO()
.
What I'm doing is converting an int to varbinary(128) so I can pass that to SET CONTEXT_INFO
. I can convert the varbinary back to int before I do the SET, but after I SET and then GET, the CONVERT always returns zero even though the varbinary value is clearly not zero.
Binary is not my strong suit, so I'm probably missing something simple.
Code
SET NOCOUNT ON
USE tempdb
GO
DECLARE @number int
DECLARE @ContextInfo varbinary(128)
SET @number = 16777216
SET @ContextInfo = CONVERT(varbinary(128), @number)
SELECT @number AS [@number]
SELECT @ContextInfo AS [@ContextInfo]
SELECT CONVERT(int, @ContextInfo) AS [CONVERT(int, @ContextInfo)]
SET CONTEXT_INFO @ContextInfo
GO
SELECT CONTEXT_INFO() AS [CONTEXT_INFO()]
SELECT CONVERT(int, CONTEXT_INFO()) AS [CONVERT(int, CONTEXT_INFO()) (Zero)]
GO
DECLARE @ContextInfo varbinary(128)
SET @ContextInfo = CONTEXT_INFO()
SELECT @ContextInfo AS [@ContextInfo]
SELECT CONVERT(int, @ContextInfo) AS [CONVERT(int, @ContextInfo) (Zero)]
GO
Result
@number
-----------
16777216
@ContextInfo
-----------------------------------
0x01000000
CONVERT(int, @ContextInfo)
--------------------------
16777216
CONTEXT_INFO()
-----------------------------------
0x0100000000000000[... more zeroes]
CONVERT(int, CONTEXT_INFO()) (Zero)
-----------------------------------
0
@ContextInfo
-----------------------------------
0x0100000000000000[... more zeroes]
CONVERT(int, @ContextInfo) (Zero)
-----------------------------------
0
Whether I try to convert it directly from CONTEXT_INFO()
or write CONTEXT_INFO()
to a variable, the result of CONVERT
is zero.
Edit: Fixed link text
Conversion Example
This example shows how an int converted to varbinary(128) will convert back without an issue, but CONTEXT_INFO() fails the conversion.
(This is for the ongoing conversation with Andomar.)
Test
DECLARE @int int
DECLARE @varBin128 varbinary(128)
SET @int = 1
SET @varBin128 = CONVERT(varbinary(128), @int)
SET CONTEXT_INFO @varBin128
SELECT CONVERT(int, @varBin128) AS [Convert @varBin128)]
SELECT CONVERT(int, CONTEXT_INFO()) AS [Convert once]
SELECT CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) AS [Convert twice]
Results
Convert @varBin128)
-------------------
1
Convert once
------------
0
Convert twice
-------------
1