SQL Server 2000 may be an older version, but many of us are still using it in our systems. In this article, we will explore how to combine the results of the sp_who2 and DBCC Inputbuffer commands to retrieve valuable information about our SQL Server instances.

First, let’s understand the problem at hand. The sp_who2 command provides us with information about the current processes running on the server, while the DBCC Inputbuffer command gives us the last executed SQL statement for a specific process. However, the challenge lies in combining these two sets of data to get a comprehensive view.

To tackle this, we will create temporary tables to store the results of sp_who2 and DBCC Inputbuffer. We will also create an additional table to hold the SQL statements and their corresponding process IDs. By using a cursor, we can loop through each process, populate the temporary table with the Inputbuffer results, and then join the statements back to the sp_who2 results.

CREATE TABLE #sp_who2 (
    SPID INT,
    Status VARCHAR(1000) NULL,
    Login SYSNAME NULL,
    HostName SYSNAME NULL,
    BlkBy SYSNAME NULL,
    DBName SYSNAME NULL,
    Command VARCHAR(1000) NULL,
    CPUTime INT NULL,
    DiskIO INT NULL,
    LastBatch VARCHAR(1000) NULL,
    ProgramName VARCHAR(1000) NULL,
    SPID2 INT
)

CREATE TABLE #SqlStatement (
    spid INT,
    statement VARCHAR(8000)
)

CREATE TABLE #temp (
    x VARCHAR(100),
    y INT,
    s VARCHAR(1000),
    id INT IDENTITY (1,1)
)

INSERT #sp_who2 EXEC sp_who2

DECLARE @spid VARCHAR(10)
DECLARE @Statement VARCHAR(8000)
DECLARE @sql VARCHAR(1000)

DECLARE SpidCursor CURSOR FOR
    SELECT spid FROM #sp_who2

OPEN SpidCursor

FETCH NEXT FROM SpidCursor INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'dbcc inputbuffer (' + @spid + ')'
    INSERT INTO #temp EXEC (@sql)
    INSERT INTO #SqlStatement SELECT @spid, s FROM #Temp WHERE id = (SELECT MAX(id) FROM #Temp)

    FETCH NEXT FROM SpidCursor INTO @spid
END

CLOSE SpidCursor
DEALLOCATE SpidCursor

SELECT B.Statement, A.* FROM #sp_who2 A LEFT JOIN #SqlStatement B ON A.spid = B.spid

DROP TABLE #Temp
DROP TABLE #SqlStatement
DROP TABLE #sp_who2

By executing the above code, we can retrieve the combined results of sp_who2 and DBCC Inputbuffer. This approach may not be as simple or efficient as using Dynamic Management Views (DMVs) available in newer versions of SQL Server, but it is still possible to achieve the desired outcome in SQL Server 2000.

It’s important to note that while SQL Server 2005 and later versions provide more streamlined methods for obtaining this information, it’s always good to have alternatives for older systems that have yet to be upgraded.

So, if you find yourself working with SQL Server 2000, don’t worry! You can still gather valuable insights by combining sp_who2 and DBCC Inputbuffer. Give it a try and see how it can benefit your SQL Server administration tasks.