Using CONTEXT_INFO() in fields default value

• wshmstr

  Mr or Mrs. 500

  Points: 592

  More actions

  September 25, 2011 at 4:55 am

  #248593

  Hi,

  There's an option called CONTEXT_INFO() for using it as @@MYGLOBALVARIABLE.

  I can use it like:

  DECLARE @userid BINARY(128)

  SET @userid = CAST(132456789 AS BINARY(128))

  SET CONTEXT_INFO @userid

  Then:

  SELECT CONVERT(BIGINT, CONTEXT_INFO())

  gives me the 123456789 as bigint.

  Everything works perfect here, I can use it whereever I want in the connection scope (this is the beauty of the CONTEXT_INFO(), assigned per connection).

  But problem starts if I want to use it in a bigint field's default value. The default value has no problems, I can directly write:

  CONVERT(BIGINT, CONTEXT_INFO())

  but the value always be null, I try to put it in a function and use function in field's default value but nothing changes.

  Thanks for your helps...

• bkubicek

  SSChampion

  Points: 10948

  More actions

  September 25, 2011 at 5:47 am

  #1386196

  wshmstr (9/25/2011)

  ---

  Hi,

  There's an option called CONTEXT_INFO() for using it as @@MYGLOBALVARIABLE.

  I can use it like:

  DECLARE @userid BINARY(128)

  SET @userid = CAST(132456789 AS BINARY(128))

  SET CONTEXT_INFO @userid

  Then:

  SELECT CONVERT(BIGINT, CONTEXT_INFO())

  gives me the 123456789 as bigint.

  Everything works perfect here, I can use it whereever I want in the connection scope (this is the beauty of the CONTEXT_INFO(), assigned per connection).

  But problem starts if I want to use it in a bigint field's default value. The default value has no problems, I can directly write:

  CONVERT(BIGINT, CONTEXT_INFO())

  but the value always be null, I try to put it in a function and use function in field's default value but nothing changes.

  Thanks for your helps...

  I think you may have a design issue here. I am not sure why you want to use context_info(). Sure it is sort of cool you can do that, but I am sure there are a lot better ways to store and save userid, which would work with a function so that the default would work on insert.

  Honestly, I would always pass known values like userid into my stored proc to do the insert. I would try to not use defaults on columns even if they exist. If you know what the value should be I would set it.

  You really need to be careful with sql connections. For instance if you have a web site, the sql connections are reused. In fact sql server is setup to reuse connections when the connection string is the same.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  September 25, 2011 at 6:14 am

  #1386201

  i think the issue is contextinfo is varbinary(128); you have to convert to nvarchar, and then to biginto to get the value you want.

  exec

  sp_help 'master.dbo.sysprocesses'

  DECLARE @var VARBINARY(128)

  SET @var = CAST(N'1234567890123456' AS VARBINARY(128))

  SET CONTEXT_INFO @var

  GO

  -- Select the context information

  DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

  SET @sess = (SELECT context_info FROM master.dbo.sysprocesses

  WHERE spid = @@spid)

  SET @var = CAST(@sess AS NVARCHAR(64))

  print @var

  SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses

  WHERE spid = @@spid

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  September 25, 2011 at 8:49 am

  #1386214

  The issue here is the conversion. Try the sample below to see a working version.

  You need to explicitly grab the correct number of bytes from the context_info() result before converting to bigint, otherwise you get an incorrect conversion.

  DECLARE @cv BIGINT

  SET @cv=1234567890123456

  SET CONTEXT_INFO @cv;

  GO

  IF OBJECT_ID('tempdb..#test1') IS NOT NULL

  DROP TABLE #test1;

  GO

  CREATE TABLE #test1(id INT IDENTITY(1,1) PRIMARY KEY,USER_ID BIGINT DEFAULT(CONVERT(BIGINT,CONVERT(VARBINARY(8),CONTEXT_INFO()))));

  GO

  INSERT

  #test1

  DEFAULT VALUES;

  GO

  SELECT *

  FROM

  #test1;

  GO

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]