September 23, 2007 at 3:41 am
Comments posted to this topic are about the item Who's Blocking
April 3, 2008 at 7:20 am
i tried running this script , right click and click in output to text but i get this error
Msg 213, Level 16, State 7, Line 4
Insert Error: Column name or number of supplied values does not match table definition.
April 2, 2009 at 6:03 am
try this:
set nocount on
declare @spid varchar(10)
declare @blkby varchar(10)
declare @stmt varchar(100)
create table #temp
( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),
dbname varchar(25), command varchar(100), cputime int, diskio int, lastbatch varchar(25),
programname varchar(255), spid2 int)
create table #temp2005
( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),
dbname varchar(25), command varchar(100), cputime int, diskio int, lastbatch varchar(25),
programname varchar(255), spid2 int, requestid int)
if @@version like 'Microsoft SQL Server 2000%' insert into #temp exec sp_who2
if @@version like 'Microsoft SQL Server 2005%' insert into #temp2005 exec sp_who2
insert into #temp select spid, status ,login, hostname , blkby ,
dbname, command , cputime , diskio , lastbatch ,
programname , spid2 from #temp2005
declare curs cursor for
select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'
open curs
fetch next from curs into @spid, @blkby
while @@fetch_status = 0
begin
set @stmt = 'dbcc inputbuffer(' + @blkby + ')'
raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait
exec (@stmt)
raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait
set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'
exec (@stmt)
fetch next from curs into @spid, @blkby
end
close curs
deallocate curs
drop table #temp
drop table #temp2005
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply