Run Job to Completion

[jjohns09]

I have a need for a job to run a many times as it takes until it is completed. What is the best way to accomplish this? Additionally, there are 34 steps in the job, so if it fails after say step 5 the first time through, I would like it to pick up at the next step.

Thank you in advance,
Jeff

 

[Catadmin]

I believe there is a precendence option called "upon completion" which works regardless of success or failure in the DTS Designer.

Additionally, if you are designing the job by right clicking Jobs (under SQL Server Agent), when you create your new steps, go to the Advance Tab and choose "Upon Success - Go to Next Step" and do the same for "Upon Failure". There's a drop down box that gives you the options of Quit Reporting Success, Quit Reporting Failure and Go to Next Step.

Does this help?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[jjohns09]

I don't think that will help. This job needs to run every 30 minutes until all of it's step have completed successfully. There is a possibility that steps 1 through 4 finish, but step 5 fails. This is where it should stop at that point because step 6 depends on data received from step 5. The next time the job executes, I want it to restart at step 5 continue on if it is successful. I don't need it to rerun steps 1 through 4 because they already completed the first go around.

This situation could repeat itself further on down in the process. Step 12 could fail and the job should terminate at that point because step 13 is dependent on 12. Next time it executes, it should start at 12.

Does this explain it a little better?

Thanks again

Jeff

 

[Catadmin]

Yes, it explains it a lot better. Unfortunately, I've never heard a way to start a job from the point of failure. As far as I know, it would simply start all over again.

I know you can notify an operator or do a net send if it succeeds or fails, but that probably doesn't help you either.

Sorry.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[ArtieChoke]

I suspect it can be done, but you would have to write a program using the DMO library, which allows you to access job info, etc.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[jjohns09]

Probably gives me the answer. Eventhough it's not the one that I want to hear.

Thank you all.

 

[dhulbert]

Not being a SQL Server Guru but just wandering is there a way you could update a record in a tabel each time a job completes sucessfully. Could you then read this record when the job starts to decide which step to start on ?

 

[Catadmin]

You can create a task which runs an Update statement upon completion of a step, but that will just add steps to your job. Additionally, I believe you could do this through the use of a stored procedure triggered when your job succeeds (or when a step succeeds). Again, though, this would involve setting it up and programming it.


Look up Update - Data Modification in Books Online for how to update columns/rows in tables. You can also loop up Stored Procedures. SQLSister has a great FAQ on recommended books for various SQL Server tasks. Click on the FAQ link in Microsoft SQL Server: Setup And Administration forum to find it.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!