SQL Scheduling Jobs

  • I really appreciate if somebody can provide me with some advice about my question.

  • Golden,

    You can't do both. You can start Job B (with no schedule) on completion of Job A OR you can have a schedule assigned to Job B that allows it to recur every 5 minutes. If you choose to recur you have to change the step completion actions to not loop. In your original post you said the steps in Job B would loop over each other. If you keep that behavior your job never stops so you can't recur, as I stated earlier.

    If you assign a schedule to Job B it has to start at some point so it will not necessarily be tied to the completion of Job A. You can still start Job B via a step in Job A but that won't initiate the new schedule for Job B. Job B will begin based on the start time of the schedule you assign, regardless of Job A's completion.

    I'm not sure what you are asking about Job C. That was meant to stop Job B but you won't need it if you use a schedule and remove the looping behavior in Job B.

    Hope this helps!

  • Hi Chris,

    FYI, I need to change my initial question to this.

    1) Job A (8 am start)

    2) Job B-start once Job A completed (until 11:59pm)

    Means that, the 1st execution time of Job B is after the completion of Job A.

    Whereas the 2nd execution time of Job B is after 5 minutes after the completion of itself. So does the 3rd and 4th execution time ( after 5 minutes)

    Regards,

    Golden

  • I may have a solution to your problem. Since you still need the dependence on Job A for Job B to start you won't be able to do this using schedules alone. I tested the following and it seems to solve your problem. It will require that you create a logging table that stores the date for each successful execution of Job A.

    Job A

    Step 1 - Perform some action

    Step 2 - Update the logging table with the current date

    Step 3 - Start Job B

    Job B

    Step 1 - Check the logging table to see if there is an entry for today's date. If there is, continue onto Step 2 because that means Job A already ran successfully. If there isn't an entry in the logging table, stop the job (EXEC msdb.dbo.sp_stop_job 'Job B') because that menas Job A still hasn't run today

    Step 2 - Perform some action

    Step 3 - Perform some action

    Using this method you can schedule Job B to start whenever you want and recur every 5 minutes until 11:59pm. The recursion will now work because under these requirements Job B actually stops when Step 3 completes. If Job B runs before 8 AM it will quit (Step 1) because Job A doesn't run successfully, and subsequently update the logging table, until 8 AM.

    Job B-Step 1 can be something like:

    DECLARE @DateRun SMALLDATETIME

    SELECT @DateRun = DateRun FROM LoggingTable WHERE DateRun = GetDate()

    IF @DateRun IS NULL

    BEGIN

    EXEC msdb.dbo.sp_stop_job 'Job B'

    END

    Hope this helps!

  • Hi Chris,

    Thanks a lot, it works

    I really appreciate your help and advice.

    Regards,

    Golden

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply