Using Session Context Information
Session context information enables applications to set binary values of up to 128 bytes that can be referenced in multiple batches, stored procedures, triggers, or user-defined functions operating on the same session. You can set a session context value by using the SET CONTEXT_INFO statement and retrieve it by using one of the following:
The CONTEXT_INFO function.
The context_info columns in the sys.dm_exec_requests and sys.dm_exec_sessions dynamic management views, and the sys.sysprocesses compatibility view.
Session context information differs from Transact-SQL variables, whose scope is limited to the current Transact-SQL batch, stored procedure, trigger, or user-defined function. Session context information can be used to store information specific to each user or the current state of the application. This can then be used to control the logic in Transact-SQL statements.
The CONTEXT_INFO function is the preferred way to retrieve the session context for the current session. You can also retrieve session context values for all current sessions and batches from the context_info columns in the sys.dm_exec_requests or sys.dm_exec_sessions dynamic management views. To select from these views requires SELECT and VIEW SERVER STATE permissions. These permissions are not required when you use the CONTEXT_INFO function.
The primary use of session context information is to set a value that can be persisted across multiple batches on the same session. The following example shows setting a value that can be referenced after several batches have been run.
-- Set context information at start.
SET CONTEXT_INFO 0x125666698456;
GO
-- Perform several nonrelated batches.
EXEC sp_helpfile;
GO
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE Name LIKE (N'Metal%')
ORDER BY ProductID;
GO
-- Select the context information set several batches earlier.
SELECT CONTEXT_INFO();
GO
Session Context Information Values
If SET CONTEXT_INFO has not yet been executed for the current session, the following session values are reported:
The CONTEXT_INFO function returns NULL.
The context_info column values in the system views are set to 128 bytes of binary zeros for the rows associated with the current session and request.
After SET CONTEXT_INFO is executed, the new value is immediately available from the following sources:
The CONTEXT_INFO function:
SELECT CONTEXT_INFO() AS MyCtxInfo;The context_info column in the sys.dm_exec_requests row associated with the current batch:
SELECT context_info AS MyCtxInfo FROM sys.dm_exec_requests WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID();The context_info column in the sys.sysprocesses row associated with the current session:
SELECT context_info AS MyCtxInfo FROM sys.sysprocesses WHERE spid = @@SPID;
The new value is not propagated to the sys.dm_exec_sessions view until the batch that contains the SET CONTEXT_INFO statement has finished running. When the batch finishes, the new value is placed in the row associated with the current session.
SELECT context_info AS MyCtxInfo
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
The following example shows when a new session context information value is visible in the system views or CONTEXT_INFO function:
-- Set a context value before the batch starts.
SET CONTEXT_INFO 0x9999
GO
-- Set a new context value in the batch.
SET CONTEXT_INFO 0x8888
-- Shows the new value available in the
-- sys.dm_exec_requests view while still in the batch.
SELECT context_info as RequestCtxInfoInBatch
FROM sys.dm_exec_requests
WHERE session_id = @@SPID
AND request_id = CURRENT_REQUEST_ID();
-- Shows the new value available from the
-- CONTEXT_INFO function while still in the batch.
SELECT CONTEXT_INFO() AS FuncCtxInfoInBatch;
-- Shows that the sys.dm_exec_sessions view still
-- returns the old value in the batch.
SELECT context_info AS SessCtxInfoInBatch
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
-- Shows the new value available in the
-- sys.sysprocesses view while still in the batch.
SELECT context_info AS ProcsCtxInfoInBatch
FROM sys.sysprocesses
WHERE spid = @@SPID;
-- End the batch.
GO
-- Shows that the sys.dm_exec_sessions view now
-- returns the new value.
SELECT context_info AS SessCtxInfoAfterBatch
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
Session Context Information and MARS
Multiple active result sets (MARS) enable applications to run multiple batches, or requests, at the same time on the same connection.
When one of the batches on a MARS connection runs SET CONTEXT_INFO, the new context value is immediately available from the following sources:
From the CONTEXT_INFO function that is run by the same batch that set the value.
The row in the sys.dm_exec_requests view that is associated with the batch that set the value.
The new context value is not propagated to the following sources until the batch that sets the value has finished running:
The CONTEXT_INFO function that is run by a batch other than the batch that set the value. CONTEXT_INFO only returns the new value in batches that are started after the batch that set the value is completed.
The row in the sys.dm_exec_sessions view that is associated with the batch that set the value.