Return Query Text Along With sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ken Simmons » Return Query Text Along With sp_who2 Using...

15 posts, Page 1 of 2

1 2 »»

Return Query Text Along With sp_who2 Using SQL 2000

Rate Topic

Display Mode

Topic Options

Author

Message

KenSimmons

Posted Thursday, July 17, 2008 8:07 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614

Comments posted to this topic are about the item Return Query Text Along With sp_who2 Using SQL 2000

Ken Simmons
http://twitter.com/KenSimmons

Post #536417

KenSimmons

Posted Thursday, July 17, 2008 8:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614

The code from the article does not copy/paste correctly, so I am posting it here. I have noticed that if you paste it into Word or Open Office it will display correctly.

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

Ken Simmons
http://twitter.com/KenSimmons

Post #536419

philcart

Posted Friday, July 18, 2008 12:35 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:53 AM
Points: 2,692, Visits: 1,074

How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. ;)

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club

Post #536496

Anipaul

Posted Friday, July 18, 2008 1:39 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:50 AM
Points: 4,785, Visits: 1,334

Great and very useful article. :)

Post #536530

Jogos

Posted Friday, July 18, 2008 2:46 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 04, 2013 1:39 AM
Points: 17, Visits: 109

It works fine for SQL 2005 if you add an extra column to the create statement of #sp_who2

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
, RequestId INT -- Extra column for SQL2005
)

Post #536555

rodonoghue

Posted Friday, July 18, 2008 3:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 3:10 AM
Points: 1, Visits: 16

Extremely useful - much appreciated!

Post #536568

Ken Davis

Posted Friday, July 18, 2008 5:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:58 PM
Points: 135, Visits: 354

Excellent. This will be very useful.

Post #536669

LP-181697

Posted Friday, July 18, 2008 6:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143

The only one problem, in multi-user environment and with quick transactions the time between taking is with sp_who and looping through those ids is enough that process is ended and another process assigned with this id. Then you get wrong inputbuffer output. We have it all the time with 2,000 users and 50-60 transactions per second. Sometime 10-15% of id returns wrong inputbuffer or disappeared while loop is running

Post #536698

Charles Kincaid

Posted Friday, July 18, 2008 6:53 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 772, Visits: 1,825

So would it be possible to rewrite this so as to avoid the cursor?

ATB

Charles Kincaid

Post #536715

dbishop

Posted Friday, July 18, 2008 7:14 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:48 AM
Points: 32, Visits: 132

You might do a search in Google for sp_who_3 (http://vyaskn.tripod.com/sp_who3.htm). Very robust with lots of optional parameters and already built to show the input buffer and a lot more than sp_who_2.

Post #536740

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions