We have scheduled a number of jobs in SQL Server 2000. We want these jobs to be executed in a sequential order i.e. the failure of one job should prevent the next job from running. Can someone help me on doing this or creating dependency between scheduled jobs.
+2
A:
You could define your jobs as steps of one single job. So you'll can specify on every step if the next step should be executed in case of error.
splattne
2009-01-29 11:41:04
I can't over emphasise how much I agree with this. I have seen complex ETLs with a number of jobs and when it goes wrong the support guys need very detailed knowledge to fix. A single job with multiple steps is much more manageable.
Chris Needham
2009-01-29 11:56:15
A:
I've done this in a queue system to cache data where there were 4 or 5 steps involved and had to allow delays for replication between the steps.
It was rather time consuming to implement as there were parent tasks which spawned 1 to n child steps which sometimes needed to be executed in order, sometimes irrelevant.
If you go down this path, you then need to create a location for error messages and process logs.
I highly recommend if in any way it can be created as one job with multiple steps, you should use the existing jobs agent. Each individual step can be configured to exit on fail, continue on fail, email on fail, etc - It's rather flexible.
John
2009-01-29 11:55:36