How do you access the Context_Info() variable in SQL2005 Profiler?

Question

I am using the Context_Info() variable to keep track of the user that is executing a stored procedure and free-form sql. When troubleshooting issues on this server everyone session comes through. I would like to be able to bring in the value of the context_info() variable and filter based on it.

Answer 1

You can use the UserConfigurable Events along with sp_trace_generateevent (EventId's 82-91) when setting the context_info() to output the values to the trace. Your option is to either do that, or trace the statements setting the context_info(). You won't be able to get the value any other way unless you write a process to dump the output of sys.dm_exec_sessions in a loop while the trace is running:

select session_id, cast(context_info as varchar(128)) as context_info
from sys.dm_exec_sessions
where session_id > 50 -- user sessions

for SQL 2000 you can use sysprocesses:

select spid, cast(context_info as varchar(128)) as context_info
from sysprocesses
where sid > 50 -- user sessions