CURSOR READ SP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » CURSOR READ SP_WHO2

19 posts, Page 1 of 2

1 2 »»

CURSOR READ SP_WHO2

Rate Topic

Display Mode

Topic Options

Author

Message

pezhman123

Posted Sunday, February 22, 2009 8:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 7:18 AM
Points: 5, Visits: 12

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

Post #662197

Ninja's_RGR'us

Posted Sunday, February 22, 2009 9:57 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 21,376, Visits: 9,584

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.

Post #662205

Jeff Moden

Posted Sunday, February 22, 2009 8:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 33,110, Visits: 27,036

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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #662338

Jeff Moden

Posted Monday, February 23, 2009 6:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 33,110, Visits: 27,036

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

Post #662494

pezhman123

Posted Monday, February 23, 2009 8:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 7:18 AM
Points: 5, Visits: 12

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 INT DECLARE @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

Post #662658

Jeff Moden

Posted Monday, February 23, 2009 8:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 33,110, Visits: 27,036

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

Post #662675

Jeff Moden

Posted Monday, February 23, 2009 8:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 33,110, Visits: 27,036

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

Post #662678

pezhman123

Posted Monday, February 23, 2009 9:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 7:18 AM
Points: 5, Visits: 12

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

Post #662692

Ninja's_RGR'us

Posted Monday, February 23, 2009 9:48 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 21,376, Visits: 9,584

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.

Post #662730

Jeff Moden

Posted Monday, February 23, 2009 10:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 33,110, Visits: 27,036

That would be correct... and the OPENROWSET solution I provided took that into account by using a discrete SELECT list.

--Jeff Moden

Post #662774

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions