I am trying to get information from the context_info column using
Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below:
However, for some rows, the context_info column is empty. Does anyone know why this is?
The database is a Dynamics AX database and the query I'm using is:
select cast(context_info as varchar(128)) as ci,* from sys.dm_exec_sessions where program_name like '%Dynamics%'
Here is the Output, I have had to remove a couple of columns but this is pretty much how it looks. If you see, The Context_info is blank for session 63 and 87.
From CONTEXT_INFO (Transact-SQL) in the documentation:
If context_info was not set:
- In SQL Server returns NULL.
- In [Azure] SQL Database returns a unique session-specific GUID.
CONTEXT_INFO needs to be set by the application and Dynamics AX sets the value for the connections included in the session. If
CONTEXT_INFO is blank it really is because the application didn't set the property.
When AX uses some connections for a specific session one can see the setting of the
ETW traces like this:
I did several tests and I couldn't create a situation where the
CONTEXT_INFO is not set correctly, even when constructing my own
ODBCconnection or using the
Connection() class from within AX.
Since you have a situation where for some sessions
CONTEXT_INFO it really looks like you have an Object Server somewhere that isn't configured to set the context information for it's sessions.
If you look closely at your results, you will see that both empty rows have the same host process id, and you don't have any rows where that process id does return a context_info.
According to the documentation on sys.dm_exec_sessions the
host_process_id is the process id of the client application.
Process ID of the client program that initiated the session. The value is NULL for internal sessions. Is nullable.
What I think is happening is that you have either multiple AX environments on this server, or you have multiple AOS instances where one of them isn't configured according to the article you linked.
For example I have a set-up with a couple of instances on one machine, connecting to a SQL Server and when querying the SQL Server
CONTEXT_INFO is blank for the instances where I didn't configure it, and never blank for the instance I did configure correctly.
Show up in the sessions on SQL Server:
However, since I only configured one of the instances if I look at the context info query I get this result (with a bunch of blanks)
My conclusion is that you likely have an instance somewhere where this registry configuration is not properly activated.