Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return Query Text Along With sp_who2 Using Dynamic Management Views


Return Query Text Along With sp_who2 Using Dynamic Management Views

Author
Message
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 2614
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
sheetalsh
sheetalsh
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
DanKennedy
DanKennedy
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1618 Visits: 389
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
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 2614
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
leea
leea
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 34
I so wish this worked with 2000



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54171 Visits: 40389
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sheetalsh
sheetalsh
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
Marc Bujold
Marc Bujold
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
richard_jm
richard_jm
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
How close to this script can you get in SQL Server 2000? I'm stuck there for awhile

Thanks!
Jerry Hung
Jerry Hung
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 1208
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
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