views:

202

answers:

3

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
A: 

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.

Andomar
That worked! But why does the first part of the test script work? There, I'm doing a CONVERT to varbinary(128) and then converting it back. That works fine.
Rob Garrison
As you can see from your second select statement, CONVERT(varbinary(128),1) actually returns a 4 byte block. Converting that back to a 4 byte int works fine.
Andomar
See my example code appended above. The varbinary(128) that comes from CONTEXT_INFO() acts differently than a varbinary(128) variable.
Rob Garrison
Well, "CONVERT(varbinary(128), @int)" is actually a *4* byte varbinary(128). When you pass it through CONTEXT_INFO, you end up with a *128* byte varbinary(128). The length of the varbinary is lost in translation.
Andomar
Thanks for the explanation. I'm still not sure that the "4 byte varbinary(128)" makes sense, but I see the affects.
Rob Garrison
Andomar, sorry for "unaccepting" your answer, but there is more to it so I wrote it up myself. I'm sure I've broken some basic rule netiquette.
Rob Garrison
No problem. Binary works because it has only one length; varbinary has a length and a maximum length. So of course binary passes through CONTEXT_INFO without loss.
Andomar
A: 

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

gbn
A: 

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.

Rob Garrison