Edit

Share via

SESSION_CONTEXT (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Returns the value of the specified key in the current session context. The value is set by using the sp_set_session_context procedure.

Transact-SQL syntax conventions

Syntax

SESSION_CONTEXT(N'key')

Arguments

'key'

The key of the value being retrieved. key is sysname.

Return types

sql_variant

Return value

The value associated with the specified key in the session context, or NULL if no value is set for that key.

Permissions

Any user can read the session context for their session.

Remarks

The MARS behavior for SESSION_CONTEXT' is similar to that of CONTEXT_INFO. If a MARS batch sets a key-value pair, the new value isn't returned in other MARS batches on the same connection unless they started after the batch that set the new value completed. If multiple MARS batches are active on a connection, values can't be set as read_only. This prevents race conditions and nondeterminism about which value wins.

Examples

The following simple example sets the session context value for key user_id to 4, and then uses the SESSION_CONTEXT function to retrieve the value.

EXECUTE sp_set_session_context 'user_id', 4;

SELECT SESSION_CONTEXT(N'user_id');

Known issues

Issue Date discovered Status Date resolved
An Access Violation (AV) exception might occur with the SESSION_CONTEXT function under certain conditions. You might encounter AV exceptions or wrong results when the SESSION_CONTEXT function runs within a parallel execution plan when the session is reset for reuse.

A fix, which was introduced in SQL Server 2019 (15.x) CU 14 to address a wrong results issue with SESSION_CONTEXT within parallel plans, was later found to cause AV exceptions under certain conditions.

To mitigate this issue, you can enable Trace Flag 11024 as a startup, global, or session trace flag. This trace flag forces SESSION_CONTEXT to execute serially, preventing it from participating in parallel query plans.

Applies to: SQL Server 2019 (15.x) CU 14 and later versions.		 January 2022 Has workaround

Related content