Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CURSOR READ SP_WHO2


CURSOR READ SP_WHO2

Author
Message
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14994 Visits: 39025
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45520 Visits: 39948
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ross McMicken
Ross McMicken
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 2196
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,.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45520 Visits: 39948
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pezhman123
pezhman123
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Dear friends,
I appreciate for all the answers , I really thank all of u
god bless you
Wink
pezhman
Ross McMicken
Ross McMicken
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 2196
I've looked, but can't find the procedure I developed. It's not too difficult, just copy the sp_who2 code to query analyzer, and make the changes to display the data you want.

I do find it interesting that many of the system sp's in SQL 2000 seem to use cursors and loops. 194 of the obejcts in master information_schema.routines contain the word cursor, while 45 contain the word loop. Maybe you need to go talk to MS about RBAR, Jeff!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45520 Visits: 39948
Ross McMicken (3/1/2009)
I've looked, but can't find the procedure I developed. It's not too difficult, just copy the sp_who2 code to query analyzer, and make the changes to display the data you want.

I do find it interesting that many of the system sp's in SQL 2000 seem to use cursors and loops. 194 of the obejcts in master information_schema.routines contain the word cursor, while 45 contain the word loop. Maybe you need to go talk to MS about RBAR, Jeff!


Heh... I noticed the same thing, Ross. Apparently, MS has the same problems that everyone else does... nasty schedules and people that can think in a Set Based fashion. sp_Space used is one of my favorite gripes.

Thanks for the feedback. And, oh by the way, here's how I "fixed" sp_SpaceUsed... I put it on some Set Based "sterioids"... BigGrin

/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.

Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)

--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC



--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14994 Visits: 39025
there you go Jeff; Call Microsoft and tell them that the premiere RE-BAR avoidance specialist is offering to rewrite any of their 194 stored procs at the everyday low price of $1,000 each...or a 50% discount to re-write all of them as a group; a few weeks work for you and you can treat yourself to some new toys.

they may not like your code, what with all your code being heavily commented, explained and readable...not really up to typical MS standards..but what the hey.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45520 Visits: 39948
Thanks, Lowell... that's an awesome compliment. They'd probably just make the same excuses as everyone else, though... things like "it's good enough for what we want to do" or "it only needs to handle a couple of rows".

Not a bad idea, though... maybe I'll send them my version of sp_SpaceUsed and tell them that, for the right price, rewrites are available on a regular basis.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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