Home > T-SQL > SQL SERVER – Working with the session context information – CONTEXT_INFO

SQL SERVER – Working with the session context information – CONTEXT_INFO


Context Information is a binary value of size up to 128 bytes that can be set for a session and referenced in multiple batches, stored procedures, triggers, or user-defined functions executing on the same session.

There are two commands that are used to set and get the context information.

SET CONTEXT_INFO – It helps to assign session context value

CONTEXT_INFO () – It helps to retrieve session context value.

We can also use DMV to see the context binary value in the current session. These are the DMVs that can use to view the context information.

  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.sysprocesses

SET CONTEXT_INFO

It sets the binary information to the session context of the current session or connection.

Syntax

SET CONTEXT_INFO  binary_string_constant

or

SET CONTEXT_INFO   @binary_variable

Example

In this example I am going to generate a binary value for a string constant and set that value to the session context.

To Generate binary value, we can use cast or convert functions. In this example, I used this statement to create the binary value of a string value.

SELECT CAST('SQL Server Rider' AS VARBINARY(50))
SET CONTEXT_INFO 0x53514C20536572766572205269646572

CONTEXT_INFO ()

This function is used to retrieve the session context value that is already set by user. Otherwise, it will return a null value. It returns 128 bytes binary value. We can use data type conversion function to convert the binary value to other type.

Syntax

CONTEXT_INFO ()

Example

In this example I am going to use the context_info() function to read the context data and convert into string value.

SET CONTEXT_INFO 0x53514C20536572766572205269646572
SELECT CAST(CONTEXT_INFO() AS varchar(100))

Output Section

This section contains the result of all the SQL query used in the examples.

pic0Using DMV to get the context information

SQL Query

SET CONTEXT_INFO 0x53514C20536572766572205269646572

SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID

Output

pic1

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: