Professional Web Applications Themes

Selecting results from a Stored Procedure - Microsoft SQL / MS SQL Server

All, Once again thanks for your help! - SQL Server 2000 I have a situation where I am wanting to look at scheduled jobs currently running on a server. To do this I am using sp_help_jobs. My aim is to be able to select the scheduled jobs that are currently running and compare the length of time they have been running with the maximum time I enter in a control table. If this has been exceeded I will then stop the job, maybe re-start it but definitely send a warning mail. I know how to do this is principle and ...

  1. #1

    Default Selecting results from a Stored Procedure

    All,

    Once again thanks for your help! - SQL Server 2000

    I have a situation where I am wanting to look at scheduled jobs currently
    running on a server. To do this I am using sp_help_jobs.

    My aim is to be able to select the scheduled jobs that are currently running
    and compare the length of time they have been running with the maximum time
    I enter in a control table. If this has been exceeded I will then stop the
    job, maybe re-start it but definitely send a warning mail.

    I know how to do this is principle and most of the steps in practice.
    However where I am failing is I don't know how to either query or use in a
    query the output from sp_help_jobs. It is a system SP in the msdb database.
    What I need to be able to do is either write the output into a table or
    essentially say "select job_name from sp_help_jobs where job_status = 1". I
    know I can't do this directly in a query, but there must be a way of doing
    is indirectly?

    Any ideas appreciated.

    As always thanks for your help it is very much appreciated!

    Steve


    Steve Guest

  2. #2

    Default Re: Selecting results from a Stored Procedure

    One option is to use create a temporary table (#Table) that matches the
    structure of the output of "sp_help_job" and then execute:
    INSERT INTO #Table EXEC masdb..sp_help_job

    This will spool the output of the stored procedure into the #Table from
    which you can do any type of query.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Steve" <net> wrote in message
    news:uP0%phx.gbl... 
    running 
    time 
    the 
    database. 



    SriSamp Guest

  3. #3

    Default Re: Selecting results from a Stored Procedure

    Thanks very much for your help!

    I'm trying the following code:

    INSERT INTO #Jobs
    EXEC msdb..sp_help_job

    SELECT * FROM #Jobs

    DROP TABLE #Jobs

    I cna't see what's wrong with it, but all I get is:

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'Jobs'.

    Any ideas?

    Many thanks!

    "SriSamp" <co.in> wrote in message
    news:eKgU$phx.gbl... [/ref]
    currently 
    > running 
    > time 
    > the [/ref]

    > database. [/ref]
    1". [/ref]
    doing 
    >
    >[/ref]


    Steve Guest

  4. #4

    Default Re: Selecting results from a Stored Procedure

    You need to create the #Jobs table inside your code, like:
    CREATE TABLE #Jobs
    (
    -- execute sp_help_job and see the columns that come in the result set
    -- Create the same columns here
    )
    INSERT INTO #Jobs EXEC msdn..sp_help_job
    -- Processing with the table
    DROP TABLE #Jobs
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Steve" <net> wrote in message
    news:%phx.gbl... [/ref]
    > currently 
    > > running 
    > > time [/ref][/ref]
    stop [/ref][/ref]
    in 
    > > database. [/ref][/ref]
    or [/ref]
    > 1". [/ref]
    > doing 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

  5. #5

    Default Re: Selecting results from a Stored Procedure

    Steve (net) writes: 

    May I guess that the error message say '#Jobs' and that you did not
    create the table?

    An alternative is to use OPENQUERY, but this is solution that has some
    cost. I have an article that describes the techniques on
    http://www.algonet.se/~sommar/share_data.html.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: Selecting results from a Stored Procedure

    It looks as if you've had your question answered, but you still may find
    this article (and the rest of the articles on Erland's site) useful:
    http://www.algonet.se/~sommar/share_data.html

    HTH,
    Bob Barrows


    Steve wrote: 



    Bob Guest

Similar Threads

  1. Pulling PRINT results from a stored procedure
    By Scott McNair in forum ASP Database
    Replies: 2
    Last Post: July 20th, 03:21 PM
  2. Inconsistant results when selecting BLOB data with 8.1
    By Reddy Rachamallu in forum IBM DB2
    Replies: 1
    Last Post: September 17th, 08:23 AM
  3. Stored Procedure Help
    By Jack in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 06:33 PM
  4. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139