Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Value set in CONTEXT_INFO not able to fetch in trigger proc Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 1:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 12:28 AM
Points: 12, Visits: 52
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.
Post #1478850
Posted Tuesday, July 30, 2013 1:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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.)

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1478855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse