Post reply

CURSOR READ SP_WHO2

  • February 22, 2009 at 8:35 am

    #206198

    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

    #948296

    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

    #948415

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 6:08 am

    #948520

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 8:45 am

    #948655

    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

    #948671

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 8:59 am

    #948672

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 9:20 am

    #948686

    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

    #948721

    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

    #948760

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 11:11 am

    #948782

    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!

  • February 24, 2009 at 7:29 pm

    #949748

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 25, 2009 at 4:21 am

    #949863

    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

    #949869

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 28, 2009 at 5:34 am

    #951500

    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