Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Return Query Text Along With sp_who2 Using SQL 2000

By Ken Simmons,

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.

Total article views: 7691 | Views in the last 30 days: 2
 
Related Articles
FORUM

sp_who2

sp_who2

FORUM

sp_who2 results show one spid 24 times

Results of sp_who2 shows ~24 lines for one spid

FORUM

dbcc inputbuffer doesn't providefull stmt?

dbcc inputbuffer

FORUM

CURSOR READ SP_WHO2

CURSOR READ SP_WHO2

ARTICLE

Return Query Text Along With sp_who2 Using Dynamic Management Views

This article describes how to generate the sp_who2 results including the query text for the spid.

Tags
administration    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones