The SQLSRV 2000 Blocking Tree?

[Grunners]

Anyone!

I've gone and downloaded a white paper from Quest Software that apparently allows me to identify locks. I've typed it out and gone over it again and again but I don't have the experience to work out what's wrong with it. As follows:

***************************
declare @qs_blocking_list table (
spid int,
blocked int,
loginame sysname,
nt_username sysname,
lastwaittype sysname,
waitresource sysname,
status sysname,
waittime bigint,
program_name sysname,
cmd sysname,
cpu bigint,
physical_io bigint,
hostname sysname,
dbid int
)

insert into @qs_blocking_list(
spid ,
blocked ,
loginame ,
nt_username ,
lastwaittype ,
waitresource ,
status ,
waittime ,
program_name ,
cmd ,
cpu ,
physical_io ,
hostname ,
dbid
)

select
spid,
blocked,
loginame,
nt_username,
lastwaittype,
waitresource,
status,
waittime,
program_name,
cmd,
cpu,
physical_io,
hostname,
dbid

from master.dbo.sysprocesses

set nocount off

select spid as 'spid'
, blocked as 'BlockedBYSPID'
, rtrim(loginame) as 'SQLUser'
, rtrim(nt_username) as 'NTUser'
, rtrim(lastwaittype) as 'Type'
, rtrim(waitresource) as 'Resource'
, rtrim(status) +

case when blocked >0 then ' and blocked' else " end +
case when spid in (select blocked from @qs_blocking_list) then ' and blocking'
else " end as 'Status'
, waittime as 'WaitTimeMS'
, rtrim(program_name) as 'Program'
, rtrim(cmd) as 'Command'
, cpu as 'CPU'
, physical_io as 'PhysicalIO'
, rtrim(hostname) as 'HostName'
, case
when dbid = 0 then "
else
db_name(dbid)
end as 'dbid'
from @qs_blocking_list where blocked <> 0 or (spid in (select blocked from @qs_blocking_list))
*****************************



I've parsed it in QA and get the following:
*******************************
Server: Msg 103, Level 15, State 7, Line 71
The identifier that starts with '
else
db_name(dbid)
end as 'dbid'
from @qs_blocking_list where blocked <> 0 or (spid in (select blocked from @qs_blo' is too long. Maximum length is 128.
Server: Msg 105, Level 15, State 1, Line 71
Unclosed quotation mark before the character string '
else
db_name(dbid)
end as 'dbid'
from @qs_blocking_list where blocked <> 0 or (spid in (select blocked from @qs_bloc'.
Server: Msg 170, Level 15, State 1, Line 71
Line 71: Incorrect syntax near '
else
db_name(dbid)
end as 'dbid'
from @qs_blocking_list where blocked <> 0 or (spid in (select blocked from @qs_blo'.
*************************************


If some kind soul could tell me where I'm going wrong perhaps I'll be able to understand why. It's not a typo on my part - I've been down the page with a ruler!

 

[SQL_Hell]

Hi there,

I think there are some " when there should be ' in that code and thats what the problem is.

However that code doesn't really do a lot, it basically the same as

SELECT * FROM SYSPROCESSES ORDER BY BLOCKED

Personally I just use

exec sp_who2

And check the Blkby column

 

[Grunners]

Ok, now that’s just daft. There I was fiddling about and you (as always) manage to come up with an alternative in two easy steps!

I’ve still to interpret it fully but at least it’s working now. Just, WHY so much code on the white paper?!?

Many thanks again Hell, what a goldmine this forum really is

 

[SQL_Hell]

I am glad I could be of help mate

Attached is document that will help you better understand blocking, I find this document explains everything very well