Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Ordering steps in a SQL Agent job

Generally if I need to do any form of complicated control flow I’ll end up using SSIS, however there is also a limited amount of control flow available in SQL Agent jobs. Here are a couple of examples of how it works and what you can do with it.

First here is a very basic job with 5 steps.

JobStepsControl1

The important columns here are On Success and On Failure. These tell the job where to go after the step completes. The default for a successful completion is to Go to the next step and for failure is to Quit the job reporting failure, and these are what you want 90% of the time. However, let’s say I want to use an error handling step.

First we add the Error Handling step.

JobStepsControl2

Next, for each of the other tabs, we go into the Advanced tab of the job edit screen.

JobStepsControl3

If you look in the drop down list under On failure action you will see each of the steps numbered with their names as well as Go on to next step, Quit the job reporting success and the default Quit the job reporting failure.

JobStepsControl4

In our case we want Go to step: [6] Error Handling.

JobStepsControl5

So now if any of our 5 steps fail they go immediately to our step Error Handling.

Next let’s create a loop. I’m going to rename step 2 as Load Data and as long as it succeeds I’m going to want it to keep re-running. Once it fails I want to go on to the next step.

JobStepsControl6

Now when we look at the drop down for On success action you will notice that Load Data isn’t an option so we can’t just loop directly back. So I’m going to use Step 3 as my Looper step. The Looper step doesn’t have to do anything. It just has to always succeed.

JobStepsControl7

Once we change the On Success option for steps 2 and 3 to be each other we have a loop, but how do we get to Step 4? Well, when the Load Data step fails we will go directly to Step 4 and skip the Looper step. This does mean that we have to deal with any unexpected errors separately, but we can handle that in code if needed.

JobStepsControl8

Hopefully this gives a good enough feel for how the control flow of a SQL Server Agent job works that you will be able to handle what comes along. Or at least understand what the guy before you did anyway!


Filed under: Microsoft SQL Server, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: control flow, microsoft sql server, SQL Agent Jobs, SSMS

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...