pezhman123 (2/23/2009)
Dear friend,first :
let me inform you that I got the error below while trying to
run your code please let me know what is the problem.
Msg 492, Level 16, State 1, Line 17
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate
second :
while you have a connection making complain about system delay the first thing we may have to check is to be sure no other customer locked the records he/she is looking to update
using this type of code will help us to have go and kill the blocking connection
any how , Thanks for your time
Regards
Pezhman
Dangit.... ya just gotta love MS... OPENROWSET in SQL Server 2000 took the duplicate column names of sp_Who2 just fine and still does. They apparently fixed that feature into oblivian in SQL Server 2005. So, as you did, we have to predefine the table. And, if the cursor you wrote is all you want to do, there's certainly no need for a cursor...
CREATE TABLE #sp_Who2
(
SPID INT,
Status NCHAR(30) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy CHAR(5) NULL,
DBName SYSNAME NULL,
Command NCHAR(16) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(30) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
RequestID INT
)
INSERT #sp_Who2
EXEC dbo.sp_Who2
SELECT 'SessionID '+STR(SPID,5)+' has been blocked by SessionID '+BlkBy+'. Total CPU time is about:'+STR(CPUTime,10)+'.'
FROM #sp_Who2
WHERE Status = 'Suspended'
OR BlkBY > '0'
I still haven't figure out why you're looking for things that have been 'Suspended', so I added an extra criteria to actually show some blocking if it occurs.
--Jeff Moden
Change is inevitable... Change for the better is not.