what is the right/best way to pass context_info and use context_info in trigger in entity framework?

Hoboly
Hoboly used Ask the Experts™
on
I have a stored prod to set context_info
I have a stored prod to get context_info
I have a trigger to use the saved  context_info
works very well in sql server management studio

However, when I use the 2 stored prod in entity framework.
1. set context_info
2. update some records
3.  savechanges
The trigger can't get the saved context_info at all.

When I check sql profiles, there are sp_reset_connection in between.
will it affect?

what is the right/best way to pass context_info and use context_info in trigger in entity framework?

Thanks!

ALTER procedure [dbo].[set_context_info] @var varchar(255) as 

--declare @var varchar(50);
--set @var = 'UT;SPABIY;1234567890123456789012345678901234567890';
declare @binvar binary(128);
set @binvar = convert(binary(128),@var);
set context_info @binvar;








ALTER procedure [dbo].[get_context_info] 
as

declare @bincontext_info binary(128);
declare @context_info varchar(50);
declare @temp_output varchar(50);
declare @temp varchar(50);
declare @user varchar(50);
declare @tx_key bigint;
							
							
set @bincontext_info = context_info() ;
set @context_info = convert(varchar(50), @bincontext_info);

set @temp_output = 'context_info: ' + @context_info
raiserror(@temp_output ,10,1);--temp 10, 1

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst
Commented:
you need to use same session when setting and updating the data... if somehow you get different session/connection when updating settings will be lost... what I am doing (with oracle, very similar concept) is before doing a dml, I call the sp that sets the context info, and then dml, so triggers will use those info to write audit logs...
Commented:
I found the solution.
The entity framework will reset context_info each time when I do select/delete/insert.

simply create a transaction will solve the issue

Author

Commented:
Thanks to HainKurt anyway

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial