locked
Issue with the Stored procedure to run sql agent jobs RRS feed

  • Question

  • Hello All,

    I have created a stored procedure to run the sql agent jobs. I have 3 jobs inside the store procedure. When I execute this sp all the jobs are starting at the same time. How can I change my sp so that it will execute the jobs with 5 mins delay. I have tried like this

     

    ALTER PROCEDURE [dbo].[usp_Auto_Load_Jobs]

     

    AS

     

    BEGIN

     

    EXEC MSDB.dbo.sp_start_job 'Execute Load_Cust Package'

    waitfor delay '000:03:00'

    EXEC MSDB.dbo.sp_start_job 'Execute Load_Acct Package'

    waitfor delay '000:03:00'

    EXEC MSDB.dbo.sp_start_job 'Execute Load_Agent Package'

    end

     

    But this didnt work. It is taking forever to execute. Please assist.

    Thanks

    Thursday, December 15, 2011 3:07 PM

Answers

All replies

  • Can you schedule time period for each job via GUI? You need to estimate on how much does the job run and deal accordingly 

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 15, 2011 3:22 PM
    Answerer
  • What about creating one job and have each package as new step and run next step on completion?

    Why create three jobs and then call them in one Job and then put delay, if your goal is to run after each other then create one job with multiple steps.

     

    When you say it takes forever, does it take 6 minutes or ?

     

    Thanks

    Aamir


    http://sqlage.blogspot.com/
    Thursday, December 15, 2011 3:26 PM
  • Right now I have individual jobs for each package. I want to execute all the jobs with single click and no need to click each individual job to execute.
    Thursday, December 15, 2011 4:59 PM
  • So create one job and call each package in separate steps. By one click you will be executing all steps.

    Thanks

    Aamir


    http://sqlage.blogspot.com/
    Thursday, December 15, 2011 6:34 PM
  • Your setting is 3 hours delay. This is 5 minutes wait:

    WAITFOR DELAY '00:05';
    

    Article: http://www.sqlusa.com/bestpractices/delayloop/

    Start job programmatically:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Proposed as answer by Naomi N Friday, December 16, 2011 3:48 PM
    • Marked as answer by Kalman Toth Wednesday, December 21, 2011 7:08 PM
    Friday, December 16, 2011 8:49 AM
  • If you want your jobs to be executed sequentially, instead of creating 3 separate jobs, create a single job and then -

    1. Either have separate STEP designed for each process and configure them to run after the previous intended one is over.
    2. OR write everything in a single STEP and the TSQL should follow each other in the sequence of their execution.

    This will help you in executing the job in a single click with ZERO headache of delay.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, December 16, 2011 10:52 AM
  • Vinay,

    Nothing wrong with his approach. He just has to use the correct syntax for 5 minutes delay, not 3 hours.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Edited by Kalman Toth Friday, December 16, 2011 1:29 PM
    • Proposed as answer by Naomi N Friday, December 16, 2011 3:48 PM
    • Marked as answer by Kalman Toth Wednesday, December 21, 2011 7:08 PM
    Friday, December 16, 2011 1:28 PM