Value set in CONTEXT_INFO not able to fetch in trigger proc

• mote.ajit2

  Old Hand

  Points: 308

  More actions

  July 30, 2013 at 1:15 am

  #281144

  Hello!

  I have two different procs for deleting the record and another proc for trigger which stores data in some history table.

  I am trying to set value in CONTEXT_INFO with user name and I need this value in trigger proc so that I can insert user info in History table. But in trigger I am not getting value which I have set in delete proc.

  is this because of session of delete proc is getting expired and in trigger I am getting value for totally different session??

  --This is how I set

  DECLARE @context varbinary(128)

  SET @context = CAST(@USER AS varbinary)

  SET CONTEXT_INFO @context

  --This is how I retrieve

  DECLARE @deleteUser VARCHAR(MAX)

  --SELECT @deleteUser = CAST(CONTEXT_INFO() AS varchar(MAX))

  SELECT @deleteUser = CAST(CONTEXT_INFO AS VARCHAR(MAX))FROM [master].[sys].[sysprocesses] WHERE SPID=@@SPID

  Please suggest

  Many thanks in adv.

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 30, 2013 at 1:24 am

  #1636855

  It's a little tricky to work with context_info. See here for how for examples: http://www.sommarskog.se/grantperm.html#context_info. (That's a section in a longer article, and you only need to read down to the next header.)

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]