July 23, 2009 at 9:59 am
I really appreciate if somebody can provide me with some advice about my question.
July 23, 2009 at 10:19 am
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!
July 23, 2009 at 10:35 am
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
July 23, 2009 at 12:47 pm
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!
July 24, 2009 at 5:09 am
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