SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CURSOR READ SP_WHO2


CURSOR READ SP_WHO2

Author
Message
pezhman123
pezhman123
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28859 Visits: 9671
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pezhman123
pezhman123
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pezhman123
pezhman123
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28859 Visits: 9671
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search