Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    6

    Lightbulb Unanswered: Testing job status T-SQL

    Hi all,

    I have created a DTS that launchs a job within its execution.
    I have several others steps that come after this but I want to be sure to execute them after the job is finished.

    I know that the job status can be found in sp_help_job but I didn't find a way to use it in my case.

    If you have any suggestions to test the job status in T-SQL, I would greatly appreciated.

    Thx in advance

    -thieg

  2. #2
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77

    Re: Testing job status T-SQL

    try querying sysjobhistory table in msdb database, there is a run_status column



    Originally posted by thieg
    Hi all,

    I have created a DTS that launchs a job within its execution.
    I have several others steps that come after this but I want to be sure to execute them after the job is finished.

    I know that the job status can be found in sp_help_job but I didn't find a way to use it in my case.

    If you have any suggestions to test the job status in T-SQL, I would greatly appreciated.

    Thx in advance

    -thieg

  3. #3
    Join Date
    Mar 2003
    Location
    Rio de Janeiro - Brazil
    Posts
    22
    Thieg,

    Have u learned how to get the status for a job ?

    Im looking for that.

    im searching where can I find the column current_execution_status that is showed in sp_help_job and have the value for the status.

    have u found anyway to know the current status ?
    And whats was the last result ? last_run_outcome

    tks

  4. #4
    Join Date
    Mar 2003
    Posts
    6
    Sorry, I found another way of doing it. I didn't find a way to check the status of the job.

    Good luck

    Originally posted by Tonioli
    Thieg,

    Have u learned how to get the status for a job ?

    Im looking for that.

    im searching where can I find the column current_execution_status that is showed in sp_help_job and have the value for the status.

    have u found anyway to know the current status ?
    And whats was the last result ? last_run_outcome

    tks

  5. #5
    Join Date
    Mar 2003
    Location
    Rio de Janeiro - Brazil
    Posts
    22
    I Found The answer ...

    use master
    exec sp_addlinkedserver @server = 'LocalServer',
    @srvproduct = '',
    @provider = 'SQLOLEDB',
    @datasrc = @@servername
    go

    select last_run_outcome, current_execution_status from openquery(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job')


    exec sp_dropserver 'LocalServer'

    with this query u have the last result and the current status ...
    U can change this to filter your query to your job with WHERE

Posting Permissions

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