Return Query Text Along With sp_who2 Using SQL 2000

, 2008-07-18

Introduction

This is a follow up to the article "Return Query Text Along With sp_who2 Using Dynamic Management Views". I received several comments about having the ablity to do the same thing using SQL Server 2000. Many of us are still using the previous version and would find this feature useful on the systems that have yet to be upgraded.

Examining the Query

I know sp_who2 and DBCC Inputbuffer hold the key to all the information we need to know; now the question is how to combine the data and display the results.

First of all we need a temporary table to hold the results of sp_who2 I am calling #sp_who2. Next we need a temporary table to hold the results of DBCC Inputbuffer I am calling #temp. Since DBCC Inputbuffer does not return the spid, we also need a table to hold the statement from the Inputbuffer results and the spid it belongs to. I called this one #SqlStatement.

By creating a cursor I can loop through all the spids, populate the Inputbuffer #temp table for the spid, insert the spid and the Inputbuffer results in the #SqlStatement table 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 #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

Conclusion

There you have it. It may not be as simple or efficient as using DMV's, but it is possible. Even though it is a lot easier to do things in SQL 2005, there is usually a way to achieve the same thing using 2000.

Rate

4.17 (24)

Share

Share

Rate

4.17 (24)

Related content

Logins, Users, and Roles - Getting Started

Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique.

2005-09-30 (first published: )

35,339 reads

Getting the Most Out of SQL Server 2000's Query Analyzer, Part II

Brian Kelley continues his series on getting the most out of SQL Server 2000's Query Analyzer. In this article he looks at the Object Browser and the Transact-SQL Debugger, new features in the 2000 Edition which can reduce development and troubleshooting time for DBAs and database developers.

5 (1)

2004-12-31 (first published: )

39,654 reads

Information Schema Views

SQL Server DBAs are often curious about the inner-workings of SQL Server. Indeed, it can save your job during disasters to know what's going on inside SQL Server. This article shows you how to use some of the SQL Server internal views to view some meta data about your servers.

5 (1)

2002-01-09

17,176 reads