Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
1  
sp_reset_connection shows up as a normal stored procedure, so you can see it with the RPC:Completed event. – Jon Seigel Jun 27 '12 at 16:25

migrated from stackoverflow.com Jun 27 '12 at 14:40

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.