February 22, 2009 at 8:35 am
Dear Friends,
Would you please let me know how could I read the result of the (executing SP_WHO2) with a cursor.
Best regards
Pezhman Omid Moghaddami
February 22, 2009 at 9:57 am
create temp table that matches the definition of sp_who2
insert into #temp (columns list) exec sp_who2
then read on cursors in BOL for exact cursor syntax.
February 22, 2009 at 8:46 pm
Have fun... sp_who2 has two SPID columns. Make sure you name them differently in the temp table.
Also, think about NOT using a cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 6:08 am
Sorry, didn't mean to leave you hanging...
Here's one way to read the result set of SP_WHO2 into a temp table. What ever you're doing (it would be nice if you told us what it actually is), you probably don't need a cursor. The exception might be producing a hierarchy for the BlkBy column to determine the start of the block chain and, if there isn't one, that would indicate the makings of a deadlock.
SELECT SPID,
STATUS,
Login,
HostName,
BlkBy,
DBName,
Command,
CPUTime,
DiskIO,
LastBatch,
ProgramName
INTO #MyHead
FROM OPENROWSET('SQLOLEDB','Server=yourserverinstancehere;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who2')
SELECT * FROM #MyHead
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 8:45 am
Dear Jeff Moden,
Thank you for spending time on my question.
I just like to know how could I find whitch conection blocked by whitch one after your first kindly help I wrote the code below
please direct me if any other easier why may found( correct my code to be more fast and easy plz)
Regards,
Pezhman
CREATE TABLE #sp_who2
( SPID INT, Status VARCHAR(1000) NULL,
Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL,
DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL,
DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL,
SPID2 INT, REQUESTID INT)
INSERT #sp_who2 EXEC sp_who2
DECLARE @SPID INTDECLARE @status VARCHAR(1000)DECLARE @login varchar(max)
DECLARE @hostname varchar(max) DECLARE @blkby varchar(max) DECLARE @dbname varchar(max)
DECLARE @Command varchar(1000) declare @cputime int
DECLARE @diskIO int
DECLARE @lastbatch varchar(1000)
DECLARE @programName varchar(1000)
DECLARE @spid2 int
DECLARE @requestid int
declare @con varchar(max)
DECLARE C Cursor FOR Select * from #sp_who2
OPEN C
FETCH NEXT FROM C into @SPID ,@STATUS ,@Login ,@hostname ,@blkby ,@dbname ,@Command ,@cputime,@diskIO ,@lastbatch,@programName,@spid2 ,@requestid
WHILE @@FETCH_STATUS = 0
begin
if @status ='suspended'
begin
print 'sessionid '+' '+convert (varchar,@blkby)+' has been blocked by sessionid '+' '+convert (varchar,@spid)+' the total cpu time is about :'+convert (varchar,@cputime)
end
FETCH NEXT FROM C into @SPID ,@STATUS ,@Login ,@hostname ,@blkby ,@dbname ,@Command ,@cputime,@diskIO ,@lastbatch,@programName,@spid2 ,@requestid
end
close c
deallocate c
Drop Table #sp_who2
February 23, 2009 at 8:57 am
Looks ok although I'll admit I haven't tried it... I don't have any servers where long periods of blocking occurs.
What's the output look like when there is blocking?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 8:59 am
After another quick look, you still may not need a cursor... but I'm on my way to work and will have to come back to this.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 9:20 am
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
February 23, 2009 at 9:48 am
That's what Jeff said when mentionning that there was 2 spid columns in the result set...
I'm guessing that the workaround is to manually mention all the columns in the select list (guessing since I never had to do this).
Good luck.
February 23, 2009 at 10:37 am
That would be correct... and the OPENROWSET solution I provided took that into account by using a discrete SELECT list.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 11:11 am
try this from my saved snippets::
it's a proc someone asked for so they coupd capture sp_who2 throughout the day:
CREATE PROCEDURE PR_CAPTURESP_WHO
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WHORESULTS') AND xtype in (N'U'))
CREATE TABLE WHORESULTS (
[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL
)
--table exists, insert some data
INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM WHORESULTS WHERE SPIDINT < 50
END
Lowell
February 24, 2009 at 7:29 pm
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.
February 25, 2009 at 4:21 am
It might be easier to take the code from sp_who2 and change it to generate the desired data directly. I did a modified version a couple of years ago to show only the open connections and the username/workstation data. That's one of the cool things about the system sp's - they are mostly accessible, and you can see how certain processes occur,.
February 25, 2009 at 4:43 am
Ross McMicken (2/25/2009)
It might be easier to take the code from sp_who2 and change it to generate the desired data directly. I did a modified version a couple of years ago to show only the open connections and the username/workstation data. That's one of the cool things about the system sp's - they are mostly accessible, and you can see how certain processes occur,.
Good suggestion... if you still have it, could you post it?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2009 at 5:34 am
Dear friends,
I appreciate for all the answers , I really thank all of u
god bless you
😉
pezhman
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply