Posted by laurenq uantrell on 10/01/37 11:27

Is there a way to get the EXEC ('DBCC INPUTBUFFER (BlkBy) WITH
NO_INFOMSGS')) to become a column in the resulting SELECT statement?

CREATE TABLE #SystemUsers (id int identity, SPID int, BlockerLoginame
nvarchar(255), BlockingSQL nvarchar(2000))


INSERT INTO #SystemUsers (SPID, BlockerLoginame, BlockingSQL)
SELECT
SPID,
loginame,
/* (EXEC ('DBCC INPUTBUFFER (BlkBy) WITH NO_INFOMSGS')) As BlockingSQL
??????? */
FROM master.dbo.sysprocesses

SELECT
s.spid,
status,
loginame,
hostname,
cmd,
cpu,
last_batch,
login_time,
kpid,
blocked,
x.BlockerLoginame,
waittype,
waittime,
lastwaittype,
waitresource,
dbid,
uid,
memusage,
ecid,
open_tran,
sid,
hostprocess,
nt_domain,
nt_username,
net_address,
net_library,
context_info
FROM master.dbo.sysprocesses s
LEFT OUTER JOIN #SystemUsers x ON x.SPID = s.Blocked
WHERE Blocked >0

• Next in forum: Re: Help finding the top 3 zipcodes within the top 5 counties
• Prev in forum: Re: Syntax to add records if primary key = list
• Thread view: Re: Connecting Trace info to blocked users?

[Reply to this message]