Question about Sp_Start_Job (Job dependencies)

Question

Question about Sp_Start_Job (Job dependencies)

Rafael A. Colón

Right there with Babe

Points: 721
More actions
July 20, 2005 at 10:48 am

#111340

I will like to create a master SQL job that calls many others SQL jobs in our financial databases, using Sp_Start_Job command. Each one of these jobs have several steps, each job in the chain should not run until the previous one finishes, that is why I'm using Sp_Start_Job.
My problem testing the new Master job is that it continues executing the next step (next SQL job) even if the previous SQL job fails. I do not want that to happen in production. I read the documentation in SQL books online for Sp_Start_Job, but besides listing the parameters, it does not provide any good example for this situation.
For example the master job have two steps
Step1 : Sp_Start_Job "My SQL Job1" -- This job have 5 steps
Step 2 : Sp_Start_Job "My SQL Job2" -- This job have 10 steps

The master job is suposed to quit with failure if "my sql job1" fails for any reason, but in my test it continues to run job 2 evens if JOb1 fails.

Any input will be greatly appreciated,
Thank You,
Rafael Colón

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steven L Durfey SSC Veteran Points: 240 More actions · Answer 1

Would it make sense to have each "step" as a seperate step via one SQL Agent Job, that way you can setup "on failure" process to stop for each step?

peterhe SSChampion Points: 11469 More actions · Answer 2

sp_start_job just starts the job, it does not wait until the job is finished. So if the sub-job is started successfully, the sp returns no error.

It cannot gurantee the sub-jobs run in sequences if you do not have WAIT loop in the master job. You can check the status of the sub-job in the loop.

sara karasik SSCommitted Points: 1573 More actions · Answer 3

DTS will do this very nicely for you. Each jobs can be scheduled to run, and on completion, the next job. You can even specify two tracks, one for success and one for completion.

DTS is not only about transferring data.

Good Luck,

Sara

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

I like Sara's idea of using DTS. However, if you're going to stick with the SQL Server Agent job mechanism, the best thing to do is chain the jobs. In other words, job 1 has a step that starts job 2. Job 2 has a step that starts job 3, etc. using sp_start_job. There is a case I've had to implement such a mechanism and it's worked out rather well.

K. Brian Kelley
@kbriankelley

chris corbin SSC Enthusiast Points: 172 More actions · Answer 5

DTS is for people who don't know SQL or basic OOP programming, plain and simple =)

Toby White SSCertifiable Points: 6187 More actions · Answer 6

What does Object Oriented Programming have to do with DTS, the SQL Agent service, jobs, running multiple jobs, calling other jobs, SSIS, or anything SQL Server related at all for that matter?