0

I have two SQL Agent jobs 'A' and 'B' Job 'A' has 10 Steps and it starts at 5:00 Am as per schedule, job 'B' has 5 Steps and it Starts by using trigger in step 3 of job 'A', job 'B' should be Completed before starting of step 8 in job'A', But sometimes Job 'B' is not completed when step 8 in job'A' starts.

So, my requirement is, i want to add one step before step 8 in that i want to check the status of job'B' when job'B' is completed then step 8 should start else it should not start and it should wait till job'B' completed

How can i achieve this?

Thank you.

5

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs.

However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?

  • Thank you JamesRyan. But condition was JobA and JobB Should run parallel. – sarath Oct 9 '14 at 12:38
  • That is the same as the linked example, start 2 subjobs and wait for both to finish before going onto the next parent job step – JamesRyan Oct 9 '14 at 14:02
0

Are you allowed to modify the Jobs?

Can you remove Step 8 through 10 in 'JOB A' create a new JOB C.
Insert step 11 in 'JOB B' then call JOB C from the Job you can run 'sp_start_job' http://msdn.microsoft.com/en-us/library/ms186757.aspx

If you are looking for something more robust there's a product called Tidal enterprise Job scheduler from Cisco. It has a lot of features integrates with sql, db2, oracle, windows,unix, etc.

0

A simple approach would be to split Job A's step 8 (let's call them A8a and A8b). A8a checks B's status from the msdb tables and does a waitfor delay if B has not completed, or stops if B has errored. This gets a bit tricky in restart scenarios, or if history gets purged from the tables before A8a runs.

You can avoid the busy wait by using a flag. This can be a simple one-row, one-column table. This flag is checked and set by both jobs like this: each job reads the flag. If it is set the other job has finished and step A8b can process. If it is not set, set it and end. Job A will need a new step at A7b to do this; job B will do this as its last step. If the flag is set job B can start A8b by using sp_start_job. You'll have to fiddle with A7's statements and "On success" / "on failure" actions to get it to work. You will need a new step at the start of the whole thing (A0) to ensure the flag's correctly set for each run.

Effectively you've split your stream into 4 by doing this: A1-3, A4-7, B* and A8-10. It would be cleaner to set each up as a separate job, have A3 kick off both A4-7 and B* and then do the "is the flag set" thing at the tail of each of these parallel jobs before starting A8-10.

Of course the real solution is to get a decent scheduler. Other posters have listed some 3rd party ones.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.