Stored Procedure for listing who is running AX queries on the SQL Server

I want to share a small nugget I've created for listing who is currently running queries against AX at any point in time. It builds on two prerequisites, one of which is from one of my suggested answers on the Dynamics AX Community Forum and secondly a solution provided by Microsoft in their own blog.

For the sake of completeness I will include the "sp_lock3" stored procedure in this post, but it is only fair to include that I got it from the LearnSQLForum, posted by a Scott Wigham here:
http://forums.learnsqlserver.com/SqlServerTopic40.aspx

The sp_lock3 looks like this:

/*******************************************************
 Source:
 http://forums.learnsqlserver.com/SqlServerTopic40.aspx
 ******************************************************/

USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
    DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
    @spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */ 
    , @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */ 
)
AS

CREATE TABLE #locktable (
    spid SMALLINT
    , loginname NVARCHAR(128) 
    , hostname NVARCHAR(128)
    , dbid INT
    , dbname NVARCHAR(128) 
    , objId INT
    , ObjName  NVARCHAR(128)
    , IndId INT
    , Type NVARCHAR(4)
    , Resource NVARCHAR(16)
    , Mode NVARCHAR(8)
    , Status NVARCHAR(5)
)

SET NOCOUNT ON

IF @spid2 IS NOT NULL AND @spid1 IS NULL
    SET @spid1 = @spid2

DECLARE @object_id INT,
  @dbid INT,
  @DynamicSql NVARCHAR(255)

/***** @spid1 is  provided so show only the locks for @spid1 and @spid2 *****/ 
IF @spid1 IS NOT NULL
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS'      AND  u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)

ELSE /***** @spid1 is not provided so show all the locks *****/ 
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)

/**********************************************************************************************
    Because the locks exist in any database, you must USE  before running OBJECT_NAME 

    We use a dynamic SQL loop to loop through each row from #locktable

    A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

WHILE @dbid IS NOT NULL
    BEGIN
        SELECT @DynamicSql = 
         'USE ' + DB_NAME(@dbid) + char(13) 
         + 'UPDATE #locktable SET ObjName = OBJECT_NAME(' 
            + CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId) 
         + ' AND objid = ' + CONVERT(VARCHAR, @object_id) 

        EXEC sp_executesql @DynamicSql

        SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
        SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
    END

SELECT * FROM #locktable 
    WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table 
        AND objid > 100 -- do not return system table locks
        AND objname <> 'spt_values'
GO

If you first install this procedure, simply by running it on the SQL Server instance, it will be globally available on that instance.

Next step is to run the script below to install my "sp_whoInAx":

/************************************************************************************
sp_whoInAx    This script lists out current users running queries against 
      any Dynamics AX database on this SQL Server Engine instance.
      Please report any issues and improvements back to the author.
      
Witten By:    Tommy Skaue (email: add the @-sign between first and last name, and end with .com)
      Microsoft Dynamics AX MVP
      yetanotherdynamicsaxblog.blogspot.com
      
Version:    1.0
      
Comments:    This procedure requires sp_lock3 in order to work.
      
This script is presented "AS IS" and has no warranties expressed or implied!!!
**********************************************************************************/

USE [master]
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_whoInAx'), 'IsProcedure')) = 1
    DROP PROC [dbo].[sp_whoInAx] 
GO

CREATE PROCEDURE [dbo].[sp_whoInAx] 
AS

 CREATE TABLE #LOCKTABLE (
  SPID SMALLINT
  , LOGINNAME NVARCHAR(128) 
  , HOSTNAME NVARCHAR(128)
  , DBID INT
  , DBNAME NVARCHAR(128) 
  , OBJID INT
  , OBJNAME  NVARCHAR(128)
  , INDID INT
  , TYPE NVARCHAR(4)
  , RESOURCE NVARCHAR(16)
  , MODE NVARCHAR(8)
  , STATUS NVARCHAR(5)
 )

 INSERT INTO #locktable
 EXEC sp_lock3

 CREATE TABLE #CurrentAxSessions (
  CI VARCHAR(128) 
  , HOST_NAME VARCHAR(128)
  , SESSION_ID smallINT
  , DATABASE_ID smallINT
  , LOGIN_TIME datetime
  , STATUS VARCHAR(30)    
 )

 INSERT INTO #CurrentAxSessions
 SELECT 
  CAST(CONTEXT_INFO AS VARCHAR(128)) AS CI
  , HOST_NAME
  , SESSION_ID
  , DATABASE_ID
  , LOGIN_TIME
  , STATUS
  --, STATUS,  CPU_TIME,MEMORY_USAGE, TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME
  --, LAST_REQUEST_START_TIME, LAST_REQUEST_END_TIME, READS, WRITES, LOGICAL_READS,  OPEN_TRANSACTION_COUNT
 FROM SYS.DM_EXEC_SESSIONS   
 WHERE 1=1
  AND PROGRAM_NAME LIKE '%DYNAMICS%'
  AND CAST(CONTEXT_INFO AS VARCHAR(128)) <> ''
  
 SELECT 
  AX.CI
  ,AX.LOGIN_TIME
  ,AX.SESSION_ID
  ,AX.STATUS
  ,LT.HOSTNAME
  ,LT.DBNAME
  ,LT.OBJNAME 
 FROM #CurrentAxSessions AX
  INNER JOIN #locktable LT ON LT.DBID = AX.DATABASE_ID AND LT.SPID = AX.SESSION_ID

GO

It takes the result of sp_lock and combines it with the "AX sessions". Again, it does require that registry change mentioned in Microsofts blog, in addition to a restart of the AOS service.

To reiterate the steps for including the user in the session context, they are:

1. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\01\Original (installed configuration). The last key, Original (installed configuration), is the key name for the current server configuration. If your system uses a different configuration that the original installed configuration, navigate to the currently active configuration.
2. Create a string registry value called ‘connectioncontext’ and set the value to 1.
3. Restart the AOS.

Now you can run sp_whoInAx from any query window on that SQL Server instance, and see from the context the user id and also see what database and tables are involved.