CONTEXT_INFO() and CONVERT

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), @in开发者_StackOverflow社区t)
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

A varbinary(128) is a 128 byte block of memory. An int is a 4 byte block of memory. So you can recover the int like this:

select convert(int,convert(varbinary(4),CONTEXT_INFO()))

The var in varbinary means the actual length varies, the number in parenthesis merely specifies the maximum size. So this SELECT statement displays a 4-byte varbinary:

select convert(varbinary(128), 1)

But when you cast CONTEXT_INFO() to a varbinary(128), you really get a 128 byte varbinary. This example is a nice demonstration:

set context_info 1
select convert(int,convert(varbinary(5),context_info()))

This will print 256; the last 3 bytes of the integer 1, with a 0 byte appended.

This is how I'd do it. SUBSTRING works with binary so we don't need any intermediate conversions (to varbinary(4)):

SET CONTEXT_INFO 12345

SELECT
    CONTEXT_INFO(),
    CAST(CONTEXT_INFO() AS int),                    --zero
    /*CAST(LEFT(CONTEXT_INFO(), 4) AS int),*/       --fails
    CAST(SUBSTRING(CONTEXT_INFO(), 1, 4) AS int)    --works

Note: LEFT does not work well with binary and fails with a conversion error

The answer appears to be a combination of the recommendations from Andomar and gbn and a recommendation from one of my colleagues.

If you use binary(128) instead of varbinary(128), everything is simpler.

Query

--<< ====================================================
--<< varbinary
--<< ====================================================
DECLARE @varbin128 varbinary(128)

SET @varbin128 = CONVERT(varbinary(128), 12345)
SET CONTEXT_INFO @varbin128
SELECT
    @varbin128                                          AS [@varbin128],
    CONTEXT_INFO()                                      AS [CONTEXT_INFO()],
    CONVERT(int, @varbin128)                            AS [Convert (@varbin128)],
    CONVERT(int, CONTEXT_INFO())                        AS [Convert (CONTEXT_INFO())],
    CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) AS [Convert(x2) (CONTEXT_INFO())]

--<< ====================================================
--<< binary
--<< ====================================================
DECLARE @bin128 binary(128)

SET @bin128 = CONVERT(binary(128), 12345)
SET CONTEXT_INFO @bin128
SELECT
    @bin128                                             AS [@bin128],
    CONTEXT_INFO()                                      AS [CONTEXT_INFO()],
    CONVERT(int, @bin128)                               AS [Convert (@bin128)],
    CONVERT(int, CONTEXT_INFO())                        AS [Convert (CONTEXT_INFO())],
    CONVERT(int, CONVERT(binary(4), CONTEXT_INFO()))    AS [Convert(x2) (CONTEXT_INFO())]

Results

@varbin128 CONTEXT_INFO()   Convert (@varbin128) Convert (CONTEXT_INFO()) Convert(x2) (CONTEXT_INFO())
---------- ---------------- -------------------- ------------------------ ----------------------------
0x00003039 0x00003039000...                12345                        0                        12345

@bin128         CONTEXT_INFO()  Convert (@bin128) Convert (CONTEXT_INFO()) Convert(x2) (CONTEXT_INFO())
--------------- --------------- ----------------- ------------------------ ----------------------------
0x000...0003039 0x000...0003039             12345                    12345                            0

So, there are ways to make the convert work if you pass in a varbinary(128) value, but it requires a double-CONVERT. If you pass in a binary(128) value, it requires only a single CONVERT.