set context_info
I have req: where i need store a string value and retrive it later on. And it should be session specific. Hence I thought of using "set context_info". But when I set the value and then retrive it the values are not similar. Here is a sample where the length are different:
declare @context_info_var varbinary(128)
select @context_info_var = cast ('test' as varbinary)
print len(@context_info_var ) //5 IS PRINTED
set context_info @context_info_var
declare @name varchar(4000)
select @context_info_var = context_info from master.dbo.sysprocesses where spid=@@spid
select @name = cast (@context_info_var as varchar)
print len(@name) //30 IS PRINTED
Any pointers?
[709 byte] By [
Srik] at [2008-2-7]
Since the context_info is a fixed-length binary stream i.e., binary(128) you will have to use the exact length for converting from binary to character for example. So you will have to do:
select @name = cast (@context_info_var as varchar(4))instead of:
select @name = cast (@context_info_var as varchar)
My suggestion would be that you should store only fixed-length data in the context info. So use char instead of varchar. This way you can manipulate it later if you need to. Or pad the binary data to 128 bytes always.
Do:
select @context_info_var = cast('test' + space(128) as binary(128))
instead of:
select @context_info_var = cast('test' as varbinary)
