Professional Web Applications Themes

query job help - Microsoft SQL / MS SQL Server

I need to execute a sproc in a SQL job based upon the status of another job and have to do it this way as opposed to a new job step in one job itself (long story) So in short I need to kinda evaluate the sp_help_job for a particular job id or name and look at the current_execution_status and if its <> 1 then go ahead and run the sproc . So can someone help me with the query that has the pseudo code like this If current_execution_status of job_id/job_name <> 1 exec sproc else 'print cannot run sproc ...

  1. #1

    Default query job help

    I need to execute a sproc in a SQL job based upon the status of another job
    and have to do it this way as opposed to a new job step in one job itself
    (long story)
    So in short I need to kinda evaluate the sp_help_job for a particular job id
    or name and look at the current_execution_status and if its <> 1 then go
    ahead and run the sproc . So can someone help me with the query that has the
    pseudo code like this

    If current_execution_status of job_id/job_name <> 1
    exec sproc
    else 'print cannot run sproc as the job is still running'



    Hassan Guest

  2. #2

    Default Re: query job help

    Why don't you simply use the sp_start_job system SP to start the second job
    from the first one when the first one is finished?

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "Hassan" <com> wrote in message
    news:#phx.gbl... 
    job 
    id 
    the 


    Dejan Guest

  3. #3

    Default Re: query job help

    Well that cant work in our situation...the second job needs to be run only
    once daily as opposed to regular runs of the first job and hence want to
    make sure that when the second job runs the first one is not running in
    order for the second job to run efficiently.


    "Dejan Sarka" <si> wrote in
    message news:e#phx.gbl... 
    job 
    > job [/ref]
    itself [/ref]
    job 
    > the 
    >
    >[/ref]


    Hassan Guest

  4. #4

    Default Re: query job help

    Then IMO, although this is not recommended by MS, the easiest way would be
    to query the sysjobhistory system table directly. The structure of the table
    and what the status flags mean is described in Books OnLine.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "Hassan" <com> wrote in message
    news:phx.gbl... 
    > job [/ref][/ref]
    another [/ref]
    > itself [/ref]
    > job [/ref][/ref]
    go [/ref][/ref]
    has 
    > >
    > >[/ref]
    >
    >[/ref]


    Dejan Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 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