Creating Multi-Step and Dynamic SQL Server Agent Jobs

By: Rick Dobson   |   Updated: 2017-06-26   |   Comments (6)   |   Related: > SQL Server Agent

Problem

I am a SQL Server Developer who recently migrated to a team with data integration production and monitoring functions. A prior MSSQLTips.com article gave me a useful introduction to creating single-step SQL Server Agent jobs, but I am now ready for some examples of multi-step SQL Server Agent Jobs. Also, I would like to see jobs demonstrating how to conditionally branch around some steps.

Solution

A SQL Server Agent job can have up to hundreds of steps to meet a business requirement. It is often useful to build a solution in parts. Then, the total solution can be implemented as a multi-step job with each part of the solution running in a separate job step. The tests for each part of a solution can be thought of as unit tests, and the total solution test consisting of all parts can be thought of as a system integration test.

Two-step SQL Server Agent jobs are the most basic example of a multi-step solution. When running a multi-step job with any number of steps, you must specify what to do after the success or failure of each step. For example, if a job step fails or raises an error, you may care to quit the whole job, ignore the job step failure and progress to the next step, or ignore the job step failure and progress to another step besides the next step (for this option you will need more than a two-step job). With features like these, SQL Server Agent offers capabilities for guiding the flow of steps within jobs.

Creating a Two Step SQL Server Agent Job

In a prior tip for SQL Server Agent, a single-step job was created to insert a row into the RecordsNumericandDateTimeValues table every 5 minutes; the table resided in the for_SQL_Server_Agent_jobs database. Each insert included two columns of data: the numeric value of 1 and the datetime value when the insert took place.

Within the same prior tip, two more single-step jobs recorded summary data about the inserts per day of the job performing an insert every 5 minutes of the day. The first of these two additional single-step jobs created the summary table. The second additional single-step job populated the summary table with values based on the number of inserts per day in the RecordsNumericandDateTimeValues table.

You can start a multi-step job in the same way that you do a single step job. See the prior tip on single-step jobs for examples of how to add a step to a single-step job. The main difference between a multi-step job and single-step job is that you add one or more other steps after the first step. You can add a step to a job by clicking the New button on the Steps page when designing a SQL Server Agent Job.

The following screen shot shows the Steps page for a job named Create a two-step reporting job. This job is a very simple extension of two single-step jobs from the prior tip. The first step creates the summary table named InsertsPerDay, and the second step populates the summary table. The InsertsPerDay table is freshly created and populated each time the Create a two-step reporting job is run from its first step.

In the prior tip that used only single-step jobs, it was advisable to run the first step before invoking the second single-step job. Running the first single-step job ensured there was a summary table to populate. Furthermore, if another database user or process dropped the InsertsPerDay table after the running of the first single-step job and before the running of the second single-step job, the second single-step job would fail because of a reference to an invalid database object (the dropped InsertsPerDay table).

By combining the two single-step jobs into one two-step job, there is no opportunity for another database user or process to drop the InsertsPerDay table after it is created and before it is populated by the second step. This is because steps can succeed one another immediately upon the completion of a prior step.

Notice from the preceding screen shot that the On Success column for the first step indicates the job is to progress to the next step after the successful completion of the first step. When you are creating a job step, you can select from any of a set of options based on whether the job step succeeds or fails. These options can be exposed from the Advanced page for a job step. You define a step in the General page, and you set various advanced options, such as how to respond after a step succeeds from the Advanced page. The following screen shot shows the selection of the Go to the next step option after a step succeeds for the first job step.

The following two screen shots show the T-SQL code for each job step in the Create a two-step reporting job. Each dialog displays in its title the name of the job step for which it displays code.

The next screen shot shows the schedule dialog for the Create a two-step reporting job. This dialog applies to both steps within the job. SQL Server Agent's graphical user interface does not natively support different schedules for different job steps. As soon as one step completes successfully or fails, a multi-step job uses the On Success or On Failure settings to determine what to do next. When a multi-step job is launched by its schedule, the job always commences from the first step.

Even when a multi-step job has a schedule, you can still run it manually at any time. This capability is especially useful for debugging job steps as well as for recovering from a failure from one step in a job without having to run the whole job from its beginning. When starting a multi-step job manually, you can choose to start the job from a different step than its first step. For example, if you knew that the InsertsPerDay table already existed, you could start the Create a two-step reporting job from its second step.

Running a SQL Server Agent Multi Step Job Manually

When you run a multi-step job manually, you have to specifically select a step from which to start before clicking the Start button in a Start Job dialog. You can show the Start Job dialog by right-clicking the job in Job Activity Monitor and choosing Start Job at Step command. Next, you highlight a step name and click Start; this starts the job from the selected step. The following screen shot shows the steps in the Create a two-step reporting job with step 1 selected just before a click to the Start button.

To demonstrate the Create a two-step reporting job, an underlying job for inserting rows into the RecordsNumericandDateTimeValues table was started at the beginning of a five-minute interval after 10:40 PM on May 20, 2017 (2017-05-20 22:40:00.633). Then, the job was allowed to run until slightly past midnight on May 20, 2017. Midnight is a critical time for the Create a two-step reporting job because its schedule causes it to operate daily at midnight.

The following screen shot shows the record of inserts into RecordsNumericandDateTimeValues table. Notice there were 16 entries from the first insert through the last insert before midnight May 20, 2017. Additionally, note that the first insert on or after midnight is for an entry on the following day May 21, 2017. The sixteenth and seventeenth rows in the result set below are highlighted for your reference of the last insert before midnight and the first insert after midnight.

This next screen shot shows a query and its output for the InsertsPerDay table that is populated by the Create a two-step reporting job. It verifies that there are 16 inserts on May 20, 2017 through midnight. Additionally, it confirms there is one more insert on the next day after the schedule fires for the Create a two-step reporting job.

Configure a Four Step SQL Server Agent Job

A multi-step job catches errors within each job step. Recall that you configure responses to errors for each step through the On Success and On Failure settings for a step. Errors can be the outcome of some error condition, such as an unresolved contention for a resource, undefined objects caused by the dropping of an object, or even just a RAISERROR statement. By wrapping a RAISERROR statement within an IF statement, you can conditionally raise an error. When the condition for the IF statement is true, the error is raised. When the IF statement condition is false, then no error is raised.

The capability to conditionally raise errors within job steps and configure job flow from a step differently based on the success or failure of a step allows you to conditionally control the flow of job steps at run time. The job described within this section demonstrates one approach for implementing this kind of conditional job flow. The sample job uses a day-of-week setting as the basis for the IF statement condition.

The following screen shot shows the General page of a job named "Four step job with error branching". This job implements conditional branching based on a RAISERROR statement within an IF statement. The job has four steps:

• The first step always operates, and it passes control to the second step on success.
• The second step, in turn, passes control to the third step on success or to the fourth step on failure.
• The third step operates only when the second step completes successfully. If the second step catches an error, then control transfers to the fourth step and bypasses the third step.
• The fourth step can receive control from either the second step if an error is raised in that step or from the third step if no error is raised in the second step.

The On Success and On Failure settings for each of the four steps appear in the Steps page that is below. Notice that the On Success setting for the first three steps is Go to the next step. The On Success setting for the fourth step is Quit the job reporting success. The On Failure settings are also identical for three steps. The first, third, and fourth steps have an On Failure setting of Quit job reporting failure. The On Failure setting for the second job step directs control to Step 4 instead quitting the job and reporting failure.

The combination of the On Success and On Failure settings for the second step enable conditional job flows between steps depending on if an error occurs in the second step.

• If no error is raised in the second step, control passes to Step 3.
• If an error is raised in the second step, control passes to Step 4.

The next screen shot shows the Command entry for Step 1. There are five elements to the script.

• The first element invokes a USE statement to set the database context to the for_SQL_Server_Agent_jobs database. Recall this is a custom database created for demonstrating SQL Server Agent features.
• The second element inserts a comment ('FROM STEP 1') into the FOUR_STEP_LOG table. The table has a single column named LOG ENTRY with an nchar(100) data type.
• The third element inserts another comment into the FOUR_STEP_LOG table. This comment displays the start time for the step to the nearest second. The VARCHAR(19) restricts the number of characters shown with the 121 style setting for the CONVERT function.
• The fourth element merely waits for 10 seconds. This pause is to simulate a wait resulting from the operation of any code that performs a task within the step.
• The fifth element writes again to the FOUR_STEP_LOG table. The log entry includes the time when the comment was inserted into the log at the end of the step.

The code for Step 3 and Step 4 is nearly identical to the code shown above for Step 1. The only difference is for the second element which writes the name of the step to the FOUR_STEP_LOG table. The Step 3 code uses a comment of 'FROM STEP 3', and the Step 4 code uses a comment of 'FROM STEP 4'.

The code for Step 2 shows in the Command box of the Step 2 properties dialog below. This step has a general design that is similar to the other three steps, except it conditionally raises an error based on the day of the week and it records the name of the day that it writes to the log. Here's a summary of the six elements in the code for Step 2.

• The first element invokes a USE statement to set the database context to the for_SQL_Server_Agent_jobs database.
• The second code block includes a DECLARE statement, an IF statement, and a RAISERROR statement within a BEGIN_END block. In the example that appears below, the condition for the IF statement is based on the day-of-week name. The RAISERROR statement is invoked only on Saturday. You can modify the day-of-the-week or use multiple day names in the condition clause to control the condition when the RAISERROR fires. See this link for a review of the RAISERROR statement. You can follow this link for a summary of the DATENAME function.
• The third element of the Step 2 code is analogous to the second element of the other three job steps. This element indicates the name of the step from which a comment comes. Additionally for Step 2, the comment denotes the name of the day on which the comment was entered into the log.
• The fourth, fifth, and sixth elements of Step 2 code are identical to the third, fourth, and fifth elements of Step 1 (see the descriptions for these elements above).

Tracing Two Paths through a Multi-Step Job

There are two main paths of interest through the Four step job with error branching job, and this section traces both paths through an examination of the FOUR_STEP_LOG table. When the job runs on Saturday, the job flow goes from Step 1 to Step 2 to Step 4. When the job runs on any day besides Saturday, the job flow goes from Step 1 to Step 2 to Step 3 to Step 4.

The following screen shot shows a path through the job steps on two successive days - Saturday May 27, 2017 and Sunday May 28, 2017. The log entries for the Saturday pass through the job steps are highlighted. Notice that there are nine log entries for this pass through the job steps -- three log entries for each of the three executed steps. The fourth log entry from Step 2 indicates the job run was on Saturday. Notice that there is no log entry from Step 3 for the Saturday pass through the steps. Also, notice that the difference between the start and end times is 10 seconds for each step.

The log entries for the Sunday pass through the steps are not highlighted. There are twelve log entries for the Sunday run -- again, three log entries for each step passed through. Three of the twelve steps are from Step 3, which was not executed during the Saturday run through the job steps.

Next Steps

• The SQL Server Agent tips covered here are more advanced than an earlier tip focusing on just single-step SQL Server Agent jobs. In addition to highlighting multi-step jobs, this tip also covers how to make the pass though the steps conditional on exogenous events, such as the name of the day on which a job runs. The On Success and On Failure settings are critical to controlling the path through job steps, and you are given step-by-step instructions on how to control these settings. You should try tweaking the settings covered for your own multi-step job to confirm your ability to vary how steps handle different On Success and On Failure settings.
• The tip also drills down on how to raise errors within job steps to control conditionally how a job passes through its steps. For example, this tip's conditional example designates a different job flow only for Saturday runs through the job. You might consider changing the day to omit Step 3 for a different day, such as Friday, or a set of days, such as Saturday and Sunday.
• Of course, it would be most interesting if you adapted either of the examples to your special database development requirements. I look forward to reading your comments for this tip article as to how you extended either of the two examples covered in this tip.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips