Blog

How to control workflows using SQL Server Agent.

By using the system stored procedure sp_start_job in a SQL Agent job step you can start other SQL Agent jobs. The call to the other job will be asynchronous, meaning it will not wait for the other job to complete. It will just start the other job and report success. This behavior can be used to start and process multiple jobs in parallel.

However, if you do want to wait until the job finishes, some code is required.

In this demonstration, there will be a master SQL Agent job that orchestrates the execution of a number of other jobs. Some of them will be executed simultaneously; others will have dependencies to other jobs so that they will wait for another job to complete.

First, a simple database with just one table:

CREATE DATABASE SQLAgentWorkflows
GO

USE SQLAgentWorkflows
GO

CREATE TABLE dbo.Demo
	(
		RowDateTime DATETIME2 DEFAULT(GETDATE()) NOT NULL
		,JobStep VARCHAR(100) NOT NULL
	)
GO

My thought with this simple database / table, is to insert a row every time a SQL Agent job completes. The RowDateTime column is used to show when the row is inserted.

Next create a set of SQL Agent jobs with the following script.

The master job is called “XX_MasterJob”. It will start 5 other jobs in sequence. The first three are run asynchronously in parallel, but the second job will fail. The two last jobs will wait for some other job to complete before they continue with their “work”. The first synchronous job will wait until job 3 completes, regardless of its status. The last synchronous job will wait for job 2 to complete with success. That is, it will also fail if job 2 has failed.

Each asynchronous job will wait for a 5 second delay, so that the behavior will be more obvious.

So the result when all jobs are completed should be:

XX_MasterJob

This job is the starting point for execution of the other jobs. This job will simply start the other jobs and complete with success. Run time should be 0 or perhaps 1 second.

XX_Subjob_Async01

This job will insert a row in dbo.Demo after the 5 second delay.

XX_Subjob_Async02_Will_Fail

There is a T-SQL syntax error in this job, so the job will fail and never insert a row into dbo.Demo.

XX_Subjob_Async03

This job will insert a row in dbo.Demo after the 5 second delay. The row should be inserted at the same time as job XX_Subjob_Async01, since they are executed simultaneously.

XX_Subjob_Sync01_Waits_for_XX_Subjob_Async03

This job waits until XX_Subjob_Async03 is completed. Then, a row is inserted in dbo.Demo. The row should be inserted about 5 seconds after XX_Subjob_Async03

XX_Subjob_Sync01_Waits_for_XX_Subjob_Async03

This job waits for XX_Subjob_Async02_Will_Fail, not only to complete, but also to complete with success. A row will never be inserted into dbo.Demo.

Right click XX_MasterJob and start job at step 1. Now, if you are fast, you can see the parallel execution in the Job Activity Monitor:

The master job will probably already have finished starting the other jobs (unless you’re really fast). If you wait a while and refresh the view, all jobs will have finished. As expected two of them have failed:

In the dbo.Demo, the results should look like this:

As you can see, the master job finished first. Second are the both asynchronous jobs. They should have about the same time stamp. Lastly, the job that job that waits for XX_Subjob_Async03 to finish should be inserted about 5 seconds later. The two jobs that failed don’t appear.

The code that controls the “dependency behavior” can be seen in the two last jobs. Of course, the wait could be done in the master job as well.

To adopt the code to your environment, you need to make the following changes (based on XX_Subjob_Sync02_Waits_for_XX_Subjob_Async02_to_complete_with_success):

1. Replace the temp table name XX_Subjob_Sync02_Waits_for_XX_Subjob_Async02_to_complete_with_success with #tmp_. This is to separate several temp tables from each other if you use several job steps.

2. Replace the job name filter XX_Subjob_Async02_Will_Fail with the job name you want to wait for.

3. Change the ;THROW 50000, ‘The XX_Subjob_Async02_Will_Fail failed!’ to some error that makes sense to you.