locked
SQL Server Jobs Scheduling RRS feed

  • Question

  • Hi...We have 2 Different Jobs scheduled in 2 Servers....The Job in Server A Starts at 12:00AM and it generally takes 1hr...The second Job in Server B Starts at 1 AM. Both the Jobs are technically related in such a way that job in server B should start after the Job in Server A finished execution. But sometimes The Job in Server A takes like 5 hrs. In this case all the things are getting screwed up as the Job in B will start before A completes execution.  

       So, Is there any way that we can start the Job in Server B immedietely after Job in server A finished execution?? may be like triggers or something...!!! Please advise me....

      Thank you
    Tuesday, February 10, 2009 7:31 PM

Answers

  • Another option would be to add a T-SQL task to the Job A and use sp_start_job stored procedure to start the Job on Server B. This way you can control the execution. You would have to make sure that the you provide the necessary permissions to the account running the job i.e. SQL Server Agent to initiate the job on the second server. The permissions required to run the SP is mentioned in the BOL link given for the SP.

    HTH
    -Amit
    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    • Marked as answer by SQLmaddy Wednesday, February 11, 2009 3:13 PM
    Tuesday, February 10, 2009 11:52 PM

All replies

  • I can think of two options off the top of my head.

    1. Use a Maintenance plan or a SSIS package to coordinate the two jobs.  You can add two SQL Agent tasks for two different servers.

    2. In step 1 of the Server B job, poll Server A's SQL Agent tables in MSDB to determine if the job is running.  If it is still running, WAITFOR 1 minute, and then poll again (T-SQL WHILE loop).  Break out of the loop if it's not running, and move to the next step.  

    I don't really have a perference between the two options.  The first option is harder to control - the job on Server B may start before 1am.  The second option will start the job on Server B at 1am, but sit around waiting until the job on Server A is finished.

    Tuesday, February 10, 2009 10:44 PM
    Answerer
  • Another option would be to add a T-SQL task to the Job A and use sp_start_job stored procedure to start the Job on Server B. This way you can control the execution. You would have to make sure that the you provide the necessary permissions to the account running the job i.e. SQL Server Agent to initiate the job on the second server. The permissions required to run the SP is mentioned in the BOL link given for the SP.

    HTH
    -Amit
    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    • Marked as answer by SQLmaddy Wednesday, February 11, 2009 3:13 PM
    Tuesday, February 10, 2009 11:52 PM
  •  Thank you for your replies....sp_start_job proc works for me...Thanks a lot...
    Wednesday, February 11, 2009 3:13 PM
  • I got another Issue on this...I am able to execute the job, but I m getting the following error....

    Executed as user: Name\LoginName. Invalid object name 'tlcspifitems'. [SQLSTATE 42S02] (Error 208).  The step failed.

      I have to run the job manually again....I tried using dbo.tablename and the login has access to the other server....but I have been getting this error...any thoughts on this??
    Thursday, February 19, 2009 7:08 PM