Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CURSOR READ SP_WHO2 Expand / Collapse
Author
Message
Posted Sunday, February 22, 2009 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, February 22, 2009 9:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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
Posted Sunday, February 22, 2009 8:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662338
Posted Monday, February 23, 2009 6:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662494
Posted Monday, February 23, 2009 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, February 23, 2009 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662675
Posted Monday, February 23, 2009 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662678
Posted Monday, February 23, 2009 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, February 23, 2009 9:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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
Posted Monday, February 23, 2009 10:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
That would be correct... and the OPENROWSET solution I provided took that into account by using a discrete SELECT list.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662774
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse