SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ken Simmons / Return Query Text Along With sp_who2 Using SQL 2000 / Latest Posts

RE: Return Query Text Along With sp_who2 Using SQL 2000

Martin-450785 — Thu, 07 Aug 2008 07:15:52 GMT

Ken,Thank you Ken for supplying us with your briliant script compilation for the SQL Activity Process Monitoring. Works like a charm... ;)CheersMartin

RE: Return Query Text Along With sp_who2 Using SQL 2000

KenSimmons — Tue, 22 Jul 2008 04:50:15 GMT

Lewis,The Dynamic Management Views are not available in SQL 2000, but I agree if you are using 2005 DMV's are the way to go.

RE: Return Query Text Along With sp_who2 Using SQL 2000

Lewis Dowson — Tue, 22 Jul 2008 03:08:13 GMT

Better solution, without cursors or temp tablesselect distinct s.session_id as spid, cast( convert(varchar, dateadd(second, datediff(ss, s.last_request_start_time, getdate()),'' ), 108) as varchar(8))[delta], r.cpu_time / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [cpu/sec], (r.reads + r.writes) / nullif(datediff(ss, s.last_request_start_time, getdate()),0) [diskio/sec], isnull(w.blocking_session_id, 0) as blockedby, s.host_name, s.login_name as login, db_name(r.database_id) as dbname, s.program_name, s.client_interface_name, s.status, r.cpu_time as cpu, r.granted_query_memory as memory, r.reads, r.writes, r.logical_reads, r.row_count, cast(r.percent_complete as dec(4,1)) as pct_complete, r.command, t.text, c.client_net_addressfrom sys.dm_exec_sessions sleft join sys.dm_os_waiting_tasks w on s.session_id = w.session_idleft join sys.dm_exec_requests r on s.session_id = r.session_idleft join sys.dm_exec_connections c on s.session_id = c.session_idouter apply sys.dm_exec_sql_text(plan_handle) twhere s.status != 'sleeping' -- // no inactive processesand s.session_id != @@spid

RE: Return Query Text Along With sp_who2 Using SQL 2000

David Bird — Fri, 18 Jul 2008 12:08:17 GMT

Your approach for combining the output from a stored procedure and a DBCC Command is very helpful. Thanks for your article.

RE: Return Query Text Along With sp_who2 Using SQL 2000

KenSimmons — Fri, 18 Jul 2008 08:50:09 GMT

[quote][b]philcart (7/18/2008)[/b][hr]How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. ;)[/quote]Here is the fn_get_sql version, but it will strip out any statement that may have a password. Also, you have to change the "Maximum characters per column" in query analyzer to more than 256 to see the results.[code]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 (dbid varchar(100), objectid varchar(100), number varchar(100), encrypted varchar(100), stmt varchar(8000), id int identity (1,1))INSERT #sp_who2 EXEC sp_who2 Declare @spid varchar(10)Declare @Statement varchar(8000)declare @sql varchar(1000)DECLARE @Handle binary(20)DECLARE SpidCursor Cursor FOR Select spid from #sp_who2OPEN SpidCursorFETCH NEXT FROM SpidCursorINTO @spidWHILE @@FETCH_STATUS = 0BEGIN SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @spid insert #temp SELECT * FROM ::fn_get_sql(@Handle) Insert Into #SqlStatement Select @spid, stmt From #Temp where id = (Select max(id) from #Temp) FETCH NEXT FROM SpidCursor INTO @spidENDClose SpidCursorDeallocate SpidCursorSelect B.Statement, A.* from #sp_who2 A Left JOIN #SqlStatement B ON A.spid = B.spidDrop Table #TempDrop Table #SqlStatementDrop Table #sp_who2[/code]

RE: Return Query Text Along With sp_who2 Using SQL 2000

dbishop — Fri, 18 Jul 2008 07:14:05 GMT

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.

RE: Return Query Text Along With sp_who2 Using SQL 2000

Charles Kincaid — Fri, 18 Jul 2008 06:53:59 GMT

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

RE: Return Query Text Along With sp_who2 Using SQL 2000

LP-181697 — Fri, 18 Jul 2008 06:27:53 GMT

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

RE: Return Query Text Along With sp_who2 Using SQL 2000

Ken Davis — Fri, 18 Jul 2008 05:52:05 GMT

Excellent. This will be very useful.

RE: Return Query Text Along With sp_who2 Using SQL 2000

rodonoghue — Fri, 18 Jul 2008 03:20:48 GMT

Extremely useful - much appreciated!

RE: Return Query Text Along With sp_who2 Using SQL 2000

Jogos — Fri, 18 Jul 2008 02:46:49 GMT

It works fine for SQL 2005 if you add an extra column to the create statement of #sp_who2CREATE 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 [b], RequestId INT [/b] -- Extra column for SQL2005 )

RE: Return Query Text Along With sp_who2 Using SQL 2000

Anipaul — Fri, 18 Jul 2008 01:39:13 GMT

Great and very useful article. :)

RE: Return Query Text Along With sp_who2 Using SQL 2000

philcart — Fri, 18 Jul 2008 00:35:49 GMT

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

RE: Return Query Text Along With sp_who2 Using SQL 2000

KenSimmons — Thu, 17 Jul 2008 20:21:51 GMT

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 intidentity (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_who2OPEN SpidCursorFETCH NEXT FROM SpidCursorINTO @spidWHILE @@FETCH_STATUS = 0BEGIN 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 @spidENDClose SpidCursorDeallocate SpidCursorSelect B.Statement, A.* from #sp_who2 A Left JOIN #SqlStatement B ON A.spid = B.spidDrop Table #TempDrop Table #SqlStatementDrop Table #sp_who2

Return Query Text Along With sp_who2 Using SQL 2000

KenSimmons — Thu, 17 Jul 2008 20:07:01 GMT

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