i have a problem with
CONTEXT_INFO in SQL Server 2008.
My problem is identical to this question: Why is my CONTEXT_INFO() empty?
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
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?
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;