Session Variable – Context_Info -SQL-Server 2005

 

Session Variable – Context_Info:

 

Session is a powerful tool in any of the programming language. SQL-Server is not a full fledge programming language but it do supports session variable for current session or connection. It stores value of session in 128 byte of binary information.

You can set or retrieve its value from CONTEXT_INFO column of the following system views.

sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

While storing the value in CONTEXT_INFO is aware that you are having 128 bit, whatever you will assign, will be stored in that 128 bit space only. Let’s have a look at it.

I am going to store two VARCHAR variable in CONTEXT_INFO. First variable will cost 6 bit and another variable cost 4 bit. If the datatype you use, is not able to implicitly convert itself to binary than you will have to do that task manually. Conversion table of datatype is given at the end of article.

Now let’s have a look at example.

USE AdventureWorks

DECLARE @Fname VARCHAR(10), @Lname VARCHAR(10), @bVar binary(128)

SET @Fname=‘Ritesh’

SET @Lname=‘Shah’

SELECT @bVar=CONVERT(binary(6),@Fname)+CONVERT(binary(4),@Lname)

SET CONTEXT_INFO @bVar

GO

We have declare two VARCHAR variable and one binary variable.  Set the values in both VARCHAR variable, convert it to binary and stored it in binary variable. Finally set the binary variable to CONTEXT_INFO column. You can query system views to get value of CONTEXT_INFO like below.

SELECT Fname = convert(varchar(10), substring(context_info, 1, 6))

FROM master..sysprocesses

WHERE spid = @@spid

 

SELECT Lname = convert(varchar(10), substring(context_info, 7, 4))

FROM master..sysprocesses

WHERE spid = @@spid

 

@@spid is used to get the exact session ID. As I explained previously also, you can get the CONTEXT_INFO column in three system views so that you can query any of the views like:

 

 

SELECT context_info

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

 

SELECT context_info

FROM sys.dm_exec_requests

WHERE session_id = @@SPID;

 

 

SELECT context_info

FROM sys.sysprocesses

WHERE spid = @@SPID;

 

Below is the conversion table I got from the SQL Server documentation.

data type conversion table

data type conversion table

Advertisements

One Response to “Session Variable – Context_Info -SQL-Server 2005”

  1. Iqbal Mohammad Says:

    Thank you so much Ritesh for this very useful information. This has helped me a lot to implement user level data access in reports. Without this I was clueless on an easy and effective implementation of Oracle’s sys_context(‘USERENV’, ‘CLIENT_INFO’) equivalent in SQLServer. Thanks again.


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: