Home Forums SQL Server 2005 Development CURSOR READ SP_WHO2 RE: CURSOR READ SP_WHO2

  • 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!