CONTEXT_INFO () and CONVERT

Question

In an attempt to build sample code for this question, I had a problem with CONTEXT_INFO() .

What I am doing is converting int to varbinary (128), so I can pass this to SET CONTEXT_INFO . I can convert varbinary back to int before I make SET, but after I set and then GET, CONVERT always returns zero, even though the varbinary value is clearly not zero.

Binary is not my strong suit, so I probably missed something simple.

The 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 

I am trying 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 conversion converted to varbinary (128) will return without a problem, but CONTEXT_INFO () does not perform the conversion.

(This is for an 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 

Answer 1

varbinary (128) is a 128 byte block of memory. Int is a 4-byte memory block. Thus, you can restore int as follows:

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

var in varbinary means that the actual length changes, the number in parentheses simply indicates the maximum size. So this SELECT statement displays a 4-byte varbinary:

 select convert(varbinary(128), 1) 

But when you enter CONTEXT_INFO () in varbinary (128), you really get a 128-byte varbinary. This example is a good demonstration:

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

256 will open; last 3 bytes of integer 1 with byte 0 added.

Answer 2

This is how I do it. SUBSTRING works with binary, so we don’t need intermediate conversions (in 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 with binary code and does not work with conversion error

Answer 3

The answer is a combination of recommendations from Andomar and gbn and the recommendation of 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 do the conversion if you pass the value varbinary (128), but this requires double-CONVERT. If you pass a binary (128) value, only one CONVERT is required for this.