Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: How to know when SQL Agent job completes

    If I use dbo.sp_startjob to start a SQL Agent Job, how do I know when it completes. Once the job starts it just continues through the following queries. I was hoping for something like openquery where SQL Server waits for the query to be completed before continuing to the next query.

    I am trying to create a stored procedure on my SQL Server machine that performs some queries on a remote Oracle Server using SSIS, then transfers the data to SQL Server. So it is important for me to somehow get SQL Server to not continue on to other queries until the job is done. Any ideas on how to approach this problem? Thanks.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    If you have access to the SSIS package, just have it insert a row into a table that you can check on a polling basis.

    Otherwise you can play around with sysjobsteps

    SELECT LAST_RUN_OUTCOME FROM MSDB..SYSJOBSTEPS
    WHERE JOB_ID = 'X'
    AND STEP_ID = <LAST STEP #>

  3. #3
    Join Date
    May 2009
    Posts
    56
    Hmm ok. I can do the polling of table. I could check the table in a while loop, and when it is done just exit the loop. Is there a way to make the while loop sleep and only check every minute or something? I don't want to it to eat up too much resources by checking.

    Does anyone else have an alternative solution? I am curious of other methods.

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Try WAITFOR

Posting Permissions

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