i have a problem with CONTEXT_INFO in SQL Server 2008.
My problem is identical to this question: Why is my CONTEXT_INFO() empty?
We use CONTEXT_INFO to store the application user in it and the use this in auditing. And in production it happens, that a lot of rows dont have the user written in, so this information is somewhere lost.
Now i want to know how can i debug\profile the setting of CONTEXT_INFO ? Where\when this info is lost/reseted?
I have SQL Server 2008 and i use the SQL Server Profiler where i only see "normal" SQL statements but not the setting of CONTEXT_INFO.
Which Columns and Events i need to enable in the Trace of SQL Profiler to see what is being there set? In the above mentioned question there is a mention of sp_reset_connection. How can i track this in SQL Profiler? Are there any other / better suited tools/ways in SQL Server to find the cause of this problem?
To set CONTEXT_INFO we use this procedure in Delphi 7. We call this once after the user has succesfully logged in.
procedure SetContextInfo( db : TDatabase; text : string );
var
query : TQuery;
begin
try
query := TQuery.Create( db.Owner );
query.DatabaseName := db.DatabaseName;
query.SQL.Add( 'DECLARE @str varchar(128)' );
query.SQL.Add( 'DECLARE @strb varbinary(128)' );
query.SQL.Add( 'SET @str=' + ConvertToSQLString( text ) );
query.SQL.Add( 'SET @strb=CAST( @str as VARBINARY(128) )' );
query.SQL.Add( 'SET CONTEXT_INFO @strb ' );
query.ExecSQL();
finally
FreeAndNil( query );
end;
end;
sp_reset_connectionshows up as a normal stored procedure, so you can see it with the RPC:Completed event. – Jon Seigel Jun 27 '12 at 16:25