Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Return Query Text Along With sp_who2 Using Dynamic Management Views Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2008 10:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 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 Dynamic Management Views

Ken Simmons
http://twitter.com/KenSimmons
Post #523842
Posted Thursday, June 26, 2008 12:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2008 1:31 AM
Points: 11, Visits: 31
Hi,

Could you please elaborate reg what you did in that query as I am not getting it?

You can please provide the acript for the tables you are using so that at least we can execute the query to see the result.

I am working in SQL server 2005.

Thanks & Regards,
SS
Post #523899
Posted Thursday, June 26, 2008 2:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:21 AM
Points: 1,588, Visits: 387
Here's a similar one I use which uses a neat trick (credit to Adam Machanic) that returns the statement as xml so it can be clicked and opened in a new window. Unfortunately it's xml encoded but very convenient.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/26/2008 09:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who3]
(
@filter tinyint = 1,
@filterspid int = NULL
)
AS
SET NOCOUNT ON;

DECLARE @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime,
current_statement_parent xml,
current_statement_sub xml)

INSERT INTO @processes
SELECT sub.*
FROM
(
SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch,
(
SELECT
LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE
) AS parent_text,
(
SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE
) AS child_text
FROM sys.sysprocesses sp WITH (NOLOCK) LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY
sub.spid

-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid

-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51
OR spid = @@SPID

-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0

SELECT spid,
blocked,
databasename,
hostname,
loginame,
status,
current_statement_parent,
current_statement_sub,
cmd,
cpu,
physical_io,
program_name,
login_time,
last_batch
FROM @processes
ORDER BY spid

RETURN 0;





Dan
www.firstcs.co.uk
Post #523947
Posted Thursday, June 26, 2008 5:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
Here is the script. I am not sure why it will not copy correctly from the article. When you copy the script from the article there is no space between "A.program_nameFROM".

SELECT
D.text SQLStatement,
A.Session_ID SPID,
ISNULL(B.status,A.status) Status,
A.login_name Login,
A.host_name HostName,
C.BlkBy,
DB_NAME(B.Database_ID) DBName,
B.command,
ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch,
A.program_name
FROM
sys.dm_exec_sessions A
LEFT JOIN
sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN
(
SELECT
A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D


Ken Simmons
http://twitter.com/KenSimmons
Post #524016
Posted Thursday, June 26, 2008 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 7, 2012 2:08 AM
Points: 10, Visits: 34
I so wish this worked with 2000


Post #524024
Posted Thursday, June 26, 2008 5:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
I've not tried this because the production environment I work in is stuck in 2k... but nice short too-the-point article. Thanks. And, I agree... I wish it worked in 2k.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #524033
Posted Thursday, June 26, 2008 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2008 1:31 AM
Points: 11, Visits: 31
Hi Ken,

It's giving error as follows:

Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Thanks,
SS
Post #524035
Posted Thursday, June 26, 2008 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 15, 2010 3:11 PM
Points: 15, Visits: 54
sheetalsh (6/26/2008)
Hi Ken,

It's giving error as follows:

Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Thanks,
SS


I get this error as well.

Marc
Post #524105
Posted Thursday, June 26, 2008 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2008 4:54 PM
Points: 1, Visits: 5
How close to this script can you get in SQL Server 2000? I'm stuck there for awhile

Thanks!
Post #524132
Posted Thursday, June 26, 2008 7:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
I copied the code from the article verbatim

ran fine in my SQL 2005 boxes (build 3042 and 2047)
I am guessing it won't run in SQL 2000


Dan: your code has a WINK in it, ha ha... when it should be the closing bracket for the TABLE declaration


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #524134
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse