Running sp_who2 as a scheduled job in sqlserver 2000 ?

• suri1984

  SSC-Addicted

  Points: 463

  More actions

  August 28, 2008 at 10:40 am

  #72723

  Hi,

  how do i schedule sp_who2 to run every 30 minutes and store that results in a file?

  Thanks

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 28, 2008 at 11:06 am

  #864589

  You can create a DTS package that runs the procedure and saves to a file and then schedule the DTS package in SQL Server Agent.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 28, 2008 at 11:29 am

  #864599

  this was a neat question; you could schedule the procedure I made belowto run every thirty minutes, and query the table WHORESULTS on demand:

  [font="Courier New"]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[/font]

  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!

• suri1984

  SSC-Addicted

  Points: 463

  More actions

  August 28, 2008 at 11:42 am

  #864604

  Thanks Lowell