SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return Query Text Along With sp_who2 Using SQL 2000


Return Query Text Along With sp_who2 Using SQL 2000

Author
Message
KenSimmons
KenSimmons
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 2614
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
KenSimmons
KenSimmons
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 2614
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
philcart
philcart
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18595 Visits: 1441
How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. Wink

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
Anipaul
Anipaul
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13427 Visits: 1407
Great and very useful article. Smile



Jogos
Jogos
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 127
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
)
rodonoghue
rodonoghue
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 18
Extremely useful - much appreciated!
Ken Davis
Ken Davis
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 492
Excellent. This will be very useful.
LP-181697
LP-181697
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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
Charles Kincaid
Charles Kincaid
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5465 Visits: 2384
So would it be possible to rewrite this so as to avoid the cursor?

ATBCharles Kincaid
dbishop
dbishop
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 252
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search