The Data You Need

Request Help

T-SQL-Driven Workflow Engine using SQL Agent jobs

By on March 22, 2019

The Summary and Apology

I began working on this blog post about six months ago and received a lot of really great feedback from really smart folks who know how to do word… stuff much better than I do. In trying to revise it I ended up just getting bogged down in life and it sat on the shelf collecting virtual dust. I have come to the conclusion that I either have to set it free, as is, or let it die and while it may not be the best structured or the most elegant prose I am still hopeful that the fundamental concepts might be useful to someone out there and if I manage to add a trick to anyone’s toolkit it will have been worth releasing.

So, that being said, if you are interested in learning one possible way to build out a workflow in T-SQL that can flow between executing steps in serial and in parallel through the spinning up and tearing down of agent jobs, then please, enjoy.

The Premise

When working with data in SQL Server it is fairly common to build out a custom sequence of events at runtime and then execute them. Most commonly I have seen this done by building out a single statement made up of the individual code statements and then just executing it.

In the following example you can see that it will always select ‘x’, then wait for 5 seconds, then depending on the client it will select y and wait 10 seconds or select z and wait 5 seconds. This process will therefore take 10-15 seconds to complete (less any additional overhead).

DECLARE @ClientID as int = 1
DECLARE @SQL as nvarchar(max)

SET @SQL = ISNULL(@SQL + ' ','') + 'select ''x''; WAITFOR DELAY ''00:00:05.000'';'

IF @ClientID = 1
	SET @SQL = ISNULL(@SQL + ' ','') + 'select ''y''; WAITFOR DELAY ''00:00:10.000'';'
ELSE
	SET @SQL = ISNULL(@SQL + ' ','') + 'select ''z''; WAITFOR DELAY ''00:00:05.000'';'

RAISERROR(@SQL,0,1) WITH NOWAIT
EXEC SP_ExecuteSQL @SQL;

A simple math question: You have 12 tasks, each taking 5 seconds each. How long does it take to complete them? If you said a minute, you are correct. How long -could- it take to complete them? If you said 5 seconds, you are also potentially right. Welcome to the difference between serial and parallel processing. If you are having to complete each task in order, one after the other, this would be running them in a serial fashion. If, however, you can complete tasks simultaneously, you are running them in parallel.

The Clumsy Analogy

Think of it like building a house in very high-level terms coming from someone who has never built a house:

  1. Floor
  2. Walls
  3. Roof
  4. Decorate Living Room
  5. Decorate Dining Room

You have to need to do a floor first, then the walls, then the roof. After the house structure is built, you can then decorate the rooms. How you go about this is determined by your available resources. Have enough people to do both at the same time? You could. If not, you would decorate one room and then the other. Decorating them at the same time is an example of handling the tasks in a parallel fashion. If you have to decorate them one after the other you are handling it in a serial fashion.

Still using the example above, if you were to add another task of ‘Throw Housewarming Party” you would want it to happen after all the rooms were decorated. The likely ideal path on that would be for steps 1-3 to be performed one after another in a serial fashion, steps 4 and 5 to be performed at the same time in a parallel fashion, and then step 6 – Throw Housewarming Party to be done.

  1. Floor
  2. Walls
  3. Roof
  4. Decorate
    1. Living Room
    2. Dining Room
  5. Throw Housewarming Party

There is a serial workflow from 1->5 with step 4 having a parallel workflow of 1->2. For a much more technical explanation that doesn’t involve clumsy house analogies, head on over to read up on Amdahl’s Law.

The question became how to accomplish flowing in and out of serial and parallel processing in SQL Server. I will be showing my approach utilizing SQL Server Agent jobs below. Shout out here to Constantine Kokkinos (b|t) for talking through possible ideas and dropping the seed of SQL Server Agent which grew into this solution.

The process I am using I have lovingly termed a task engine. Currently a task can be a stored procedure or a straight bit of code. However it could be anything that can be run as a SQL Server Agent job so a task could be an SSIS package or something you want to run as CmdExec. These require the Agent Job to be constructed using different syntax though and I have yet to build it out. I will provide a small overview of each of the components and then provide the entirety of the script to set up that component and at the end I will step through the primary engine snippet by snippet.

The Setup

The demo you will see here relies on previously defined tasks but the tasks can also be generated at runtime.

Task: This table stores the task definition as the straight bit of code or the stored procedure that should be called.

For the purposes of this demo, every code-based task is going to run:

SELECT ‘x’; WAITFOR DELAY ’00:00:05′;

and every stored procedure based task is going to call common.TestSproc which is defined as:

CREATE Procedure [common].[TestSproc] 
	@Client varchar(250) = ''
AS

	SELECT 'x'; 
	
	IF @Client = 'Test6'
		WAITFOR DELAY '00:00:10';
	ELSE 
		WAITFOR DELAY '00:00:05';

GO

The same basic code with the exception that it deviates delay time based on the Client. This is to demonstrate passing the @Client parameter and verifying that it works.

TaskList: This table stores a defined listing of tasks that should be run as a single task list. This is also where the task order is set. For example if you look at the results of the following:

SELECT * 
FROM TaskList 
WHERE Client = 'Test1' 
	AND TaskListName = 'Default'

SELECT * 
FROM TaskList 
WHERE Client = 'Test2' 
	AND TaskListName = 'Default'

SELECT * 
FROM TaskList 
WHERE Client = 'Test3' 
	AND TaskListName = 'Default'

You will see that they call the identical tasks, the only difference is that Test1 specifies the TaskOrder one after the other, Test2 specifies the same TaskOrder, and Test3 takes a more realistic approach by weaving in and out of serial.
Tasks: 1 -> 2 -> (3|4|5) -> (6|7) -> 8 -> 9 -> (10|11|12).

The full setup script is below:

USE Demo

IF EXISTS (select 'x' from information_schema.tables where table_name = 'TaskList')
	DROP TABLE TaskList

IF EXISTS (select 'x' from information_schema.tables where table_name = 'Task')
	DROP TABLE Task

CREATE TABLE Task
(
		TaskID int identity(1,1)
		CONSTRAINT PK_Task_TaskID PRIMARY KEY
	,	TaskName varchar(250)
	,	ProcedureServer varchar(50)
	,	ProcedureDB varchar(50)
	,	ProcedureSchema varchar(50)
	,	ProcedureName varchar(max)
	,	Code nvarchar(max)
	,	TaskTypeID int
)

CREATE TABLE TaskList
(
		TaskListName varchar(250)
	,	Client varchar(250)
	,	TaskID int
		CONSTRAINT FK_TaskList_Task_TaskID FOREIGN KEY REFERENCES Task(TaskID)
	,	TaskOrder int
)

INSERT INTO Task (TaskName, Code, TaskTypeID)
VALUES
 ('Test Task1','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task2','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task3','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task4','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task5','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task6','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task7','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task8','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task9','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task10','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task11','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)
,('Test Task12','SELECT ''x''; WAITFOR DELAY ''00:00:05'';',2)

INSERT INTO Task (TaskName, ProcedureDB, ProcedureSchema, ProcedureName, TaskTypeID)
VALUES 
 ('Test Stored Proc Task1','Demo','common','TestSproc',1)
,('Test Stored Proc Task2','Demo','common','TestSproc',1)
,('Test Stored Proc Task4','Demo','common','TestSproc',1)
,('Test Stored Proc Task6','Demo','common','TestSproc',1)
,('Test Stored Proc Task7','Demo','common','TestSproc',1)
,('Test Stored Proc Task9','Demo','common','TestSproc',1)

INSERT INTO Task (TaskName, Code, TaskTypeID)
VALUES 
 ('Test FailTask1','SELECT ''x'' from fail; WAITFOR DELAY ''00:00:05'';',2)

 select * from Task

--Test 1 - pure serial
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ('Default','Test1',1,1)
,('Default','Test1',2,2)
,('Default','Test1',3,3)
,('Default','Test1',4,4)
,('Default','Test1',5,5)
,('Default','Test1',6,6)
,('Default', 'Test1',7,7)
,('Default', 'Test1',8,8)
,('Default', 'Test1',9,9)
,('Default', 'Test1',10,10)
,('Default', 'Test1',11,11)
,('Default', 'Test1',12,12)

-- Test 2 - Pure parallel
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES 
 ('Default', 'Test2',1,1)
,('Default', 'Test2',2,1)
,('Default', 'Test2',3,1)
,('Default', 'Test2',4,1)
,('Default', 'Test2',5,1)
,('Default', 'Test2',6,1)
,('Default', 'Test2',7,1)
,('Default', 'Test2',8,1)
,('Default', 'Test2',9,1)
,('Default', 'Test2',10,1)
,('Default', 'Test2',11,1)
,('Default', 'Test2',12,1)

-- Test 3 - Hybrid (realistic)
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ('Default', 'Test3',1,1)
,('Default', 'Test3',2,2)
,('Default', 'Test3',3,3)
,('Default', 'Test3',4,3)
,('Default', 'Test3',5,3)
,('Default', 'Test3',6,4)
,('Default', 'Test3',7,4)
,('Default', 'Test3',8,5)
,('Default', 'Test3',9,6)
,('Default', 'Test3',10,7)
,('Default', 'Test3',11,7)
,('Default', 'Test3',12,7)

-- Test 5 - Stored procs - Slow Client
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ( 'Default', 'Test5',13,1)
,('Default', 'Test5',14,2)
,('Default', 'Test5',3,3)
,('Default', 'Test5',15,3)
,('Default', 'Test5',5,3)
,('Default', 'Test5',16,4)
,('Default', 'Test5',17,4)
,('Default', 'Test5',8,5)
,('Default', 'Test5',18,6)
,('Default', 'Test5',10,7)
,('Default', 'Test5',11,7)
,('Default', 'Test5',12,7)

-- Test 6 - Stored procs - Standard Client
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ('Default', 'Test6',13,1)
,('Default', 'Test6',14,2)
,('Default', 'Test6',3,3)
,('Default', 'Test6',15,3)
,('Default', 'Test6',5,3)
,('Default', 'Test6',16,4)
,('Default', 'Test6',17,4)
,('Default', 'Test6',8,5)
,('Default', 'Test6',18,6)
,('Default', 'Test6',10,7)
,('Default', 'Test6',11,7)
,('Default', 'Test6',12,7)

-- Example SQL injection
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ( 'Default', '; DROP TABLE Task;',1,1)
,('Default', '; DROP TABLE Task;',14,2)
,('Default', '; DROP TABLE Task;',3,3)
,('Default', '; DROP TABLE Task;',4,4)
,('Default', '; DROP TABLE Task;',5,5)
,('Default', '; DROP TABLE Task;',6,6)
,('Default', '; DROP TABLE Task;',7,7)
,('Default', '; DROP TABLE Task;',8,8)
,('Default', '; DROP TABLE Task;',9,9)
,('Default', '; DROP TABLE Task;',10,10)
,('Default', '; DROP TABLE Task;',11,11)
,('Default', '; DROP TABLE Task;',12,12)

-- Failure
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ( 'Default', 'Fail',1,1)
,('Default', 'Fail',2,2)
,('Default', 'Fail',19,3)
,('Default', 'Fail',4,4)
,('Default', 'Fail',5,5)
,('Default', 'Fail',6,6)
,('Default', 'Fail',7,7)
,('Default', 'Fail',8,8)
,('Default', 'Fail',9,9)
,('Default', 'Fail',10,10)
,('Default', 'Fail',11,11)
,('Default', 'Fail',12,12)

-- Test1 - Parallel
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ('Parallel', 'Test1',1,1)
,('Parallel', 'Test1',2,1)
,('Parallel', 'Test1',3,1)
,('Parallel', 'Test1',4,1)
,('Parallel', 'Test1',5,1)
,('Parallel', 'Test1',6,1)
,('Parallel', 'Test1',7,1)
,('Parallel', 'Test1',8,1)
,('Parallel', 'Test1',9,1)
,('Parallel', 'Test1',10,1)
,('Parallel', 'Test1',11,1)
,('Parallel', 'Test1',12,1)

-- Test1 - parallel/reduced
INSERT INTO Demo.dbo.tasklist
	(TaskListName, Client, TaskID, TaskOrder)
VALUES
 ('Reduced', 'Test1',1,1)
,('Reduced', 'Test1',3,1)
,('Reduced', 'Test1',7,1)
,('Reduced', 'Test1',11,1)


IF OBJECT_ID('[common].[TestSproc]', 'P') IS NOT NULL
	DROP PROCEDURE [common].[TestSproc] 
GO

CREATE Procedure [common].[TestSproc] 
	@Client varchar(250) = ''
AS

	SELECT 'x'; 
	
	IF @Client = 'Test6'
		WAITFOR DELAY '00:00:10';
	ELSE 
		WAITFOR DELAY '00:00:05';
GO

The Magic

Now on to the Task Engine stored procedure that contains the meat of the process. I should note that some of the things in place are just to make my life easier with my current stored tasklist-based implementation so I will only briefly touch on those.

  • @Client – This parameter specifies a Client. It, along with @TaskListName, are used to lookup against the TaskList table
  • @DeleteIfJobExists – If an Agent Job by the same name exists, delete it prior to trying to create it. Defaults to True.
  • @KeepJobOnFailure – If an Agent Job fails, controls whether it should be deleted or not. Defaults to False.
  • @KeepJobOnSuccess – If an Agent Job succeeds, controls whether it should be deleted or not. Defaults to False.
  • @ExitProcessOnFail – If a failure occurs, controls whether it should continue running the remaining tasks or exit out. Defaults to True.
  • @debug – Set to 1 to output more detailed debugging info such as generated code, etc.

It also contains a number of working parameters.

CREATE PROCEDURE common.RunTaskEngine

		@Client as varchar(250)
	,	@TaskListName as varchar(250) = 'Default'
	,	@DeleteIfJobExists bit = 1
	,	@KeepJobOnFailure bit = 0
	,	@KeepJobOnSuccess bit = 0
	,	@ExitProcessOnFail bit = 1
	
	,	@Tasks varchar(250) = null 

AS

SET NOCOUNT ON

--#region - Working parameters
	DECLARE @job_name sysname
	DECLARE @job_id UNIQUEIDENTIFIER
	DECLARE @job_owner sysname
	DECLARE @JobCompletionStatus int
	DECLARE @Start as datetime = GetDate()
	DECLARE @Success as varchar(max)
	DECLARE @Failure as varchar(max)
	DECLARE @SQL as nvarchar(max)
	DECLARE @id as int
	DECLARE @maxID as int
	DECLARE @TaskOrder as int
	DECLARE @maxTaskOrder as int
	DECLARE @TaskID as int
	DECLARE @Code as nvarchar(max)
--#endregion

The first step is to build out a list of the tasks that need to be run. In our case we are pulling it from the defined task lists but this could certainly be generated dynamically. Instead of building statements and just concatenating into a single string to be executed you could run them into a similar table. This step also builds out the code that will be placed in the Agent job. You’ll see that it turns the code-based task into SELECT ”x”; WAITFOR DELAY ”00:00:05”;   and it turns the stored procedure-based task into:

DECLARE @SQL as nvarchar(max)      
DECLARE @Client as varchar(50) = ''; DROP TABLE Task;''            

SET @SQL = N''EXEC Demo.common.TestSproc @Client = @Client''        

EXEC SP_ExecuteSQL @SQL
 , N''@Client varchar(50)''
 , @Client = @Client

Keeping in mind that these are not valid scripts stand-alone but will be when used as the command text for creating Agent jobs.

 

This section also defines the TaskOrder range so it knows what it will need to iterate through for processing.

--#region - Control Temp Table
	IF OBJECT_ID('TempDB..#Tasks') IS NOT NULL
		DROP TABLE #Tasks

	CREATE TABLE #Tasks
	(
			ID int identity(1,1)
		,	TaskName varchar(100)
		,	Code varchar(max)
		,	TaskOrder int
		,	Completed bit 
	)
--#endregion

--#region - Load logic
	INSERT INTO #Tasks(TaskName, Code, TaskOrder,Completed)

	SELECT	t.TaskName
		,	CASE 
				WHEN t.TaskTypeID = 1 THEN '
				DECLARE @SQL as nvarchar(max)
				DECLARE @Client as varchar(50) = ''''' + @Client + '''''
				
				SET @SQL = N''''EXEC Demo.common.TestSproc @Client = @Client''''

				EXEC SP_ExecuteSQL @SQL
				, N''''@Client varchar(50)''''
				, @Client = @Client 
				'
				WHEN t.TaskTypeID = 2 THEN REPLACE(t.Code,'''','''''')
				ELSE ''
			END
		,	tl.TaskOrder
		,	0 
	FROM Demo.dbo.TaskList tl 
		INNER JOIN Demo.dbo.Task t 
			ON t.TaskID = tl.TaskID

	WHERE tl.Client = @Client
		AND tl.TaskListName = @TaskListName
		AND (
			@Tasks IS NULL
			OR
			t.TaskID IN (SELECT [data] FROM UDFs.dbo.Split(@Tasks,','))
			)
		
	order by tl.TaskOrder
--#endregion

SET @TaskOrder = (SELECT MIN(TaskOrder) FROM #Tasks WHERE Completed = 0)
SET @MaxTaskOrder = (SELECT MAX(TaskOrder) FROM #Tasks WHERE Completed = 0)

IF @debug = 1
	select * from #tasks

SET @msg = 'Starting Task Order: ' + Cast(@TaskOrder AS VARCHAR) 
RAISERROR (@msg, 0, 1) WITH NOWAIT

The next section begins the processing of the defined tasks. It drops/creates a new table #Jobs which will contain the individual tasks corresponding to the task order. If you look at Test3, you’ll see that Task ID 3,4,5 are defined as TaskOrder 3 so when it is processing TaskOrder 3 there are three entries in the #Jobs table to show that those three need to be processed in parallel.

WHILE @TaskOrder <= @MaxTaskOrder
BEGIN
	--#region - Working Temp Tables
		IF OBJECT_ID('TempDB..#Jobs') IS NOT NULL
				DROP TABLE #Jobs

			CREATE TABLE #Jobs 
			(
					ID int identity(1,1)
				,	job_name varchar(250)
				,	code nvarchar(max)
				,	running bit
				,	status bit
				,	created bit
				,	StartDate datetime2(3)
				,	EndDate datetime2(3)
			)
	--#endregion

	--#region - Load jobs
		INSERT INTO #Jobs
		SELECT 
		'Client: ' + @Client + ' - ' + TaskName, Code, 0,null,0,GetDate(),null FROM #Tasks t
		WHERE TaskOrder = @TaskOrder

The next step is to create the Agent jobs and start them. This still happens in a serial fashion but it is very quick. It will generate Agent job names based on Client and Task names and use the code from earlier. One thing to note is that the code used to spin up the Agent job here is for the “Transact-SQL script (T-SQL)” job step type. If you are going with an SSIS package type or CmdExec type, the code would need to differ here. It will then issue start statements.

	--#region - Create Jobs
		WHILE EXISTS(SELECT 1 FROM #Jobs WHERE created = 0)
		BEGIN	
				
			SET @TaskID = (SELECT TOP 1 ID FROM #Jobs WHERE created = 0)
			SET @job_name = (SELECT TOP 1 job_name FROM #Jobs WHERE ID = @TaskID)
			SET @code = (SELECT TOP 1 code FROM #Jobs WHERE ID = @TaskID)

		--#region - Delete Job If It Exists (parameter based)
			IF @DeleteIfJobExists = 1 AND EXISTS (SELECT 'x' FROM msdb.dbo.sysjobs_view job WHERE [name] = @job_name)
			BEGIN
				EXEC msdb.dbo.sp_delete_job @job_name = @Job_name 
			END
		--#endregion

			SET @SQL = 
			REPLACE(REPLACE(N'
				USE msdb;

				IF NOT EXISTS (SELECT ''x'' FROM msdb.dbo.sysjobs WHERE [name] = N''{JobName}'')
				BEGIN 
					EXEC dbo.sp_add_job
						@job_name = N''{JobName}'';							

					EXEC sp_add_jobstep
						@job_name = N''{JobName}'',
						@step_name = N''Process Expired Exclusions'',
						@subsystem = N''TSQL'',
						@command = N''{Code}'', 
						@retry_attempts = 0,
						@retry_interval = 0;

					EXEC dbo.sp_add_jobserver
						@job_name =  N''{JobName}'';
				END'
				,'{JobName}',@job_name)
				,'{Code}',@Code)

			IF @debug = 1
				RAISERROR (@SQL, 0, 1) WITH NOWAIT

			EXEC SP_EXECuteSql @SQL
			
			IF @debug = 0
				SET @msg = 'Job Created - ' + @job_name
				RAISERROR (@msg, 0, 1) WITH NOWAIT

			UPDATE #Jobs SET created = 1 WHERE ID = @TaskID
		END
	--#endregion

	--#region - Start Jobs
		SET @SQL = null;
		SELECT @SQL = ISNULL(@SQL,' ') + 'EXEC msdb.dbo.sp_start_job ''' + job_name + ''';'
		FROM #Jobs
		
		
		IF @debug = 1 
			RAISERROR (@sql, 0, 1) WITH NOWAIT

		IF @debug = 0 
			RAISERROR ('Starting jobs.', 0, 1) WITH NOWAIT
			
		EXEC sp_EXECuteSQL @SQL;
	--#endregion

Executing the stored procedure with @debug = 1 gives us the following snippet so you can see an example of what is output:

Starting Task Order: 1

USE msdb;

IF NOT EXISTS (SELECT 'x' FROM msdb.dbo.sysjobs WHERE [name] = N'Client: ; DROP TABLE Task; - Test Task1')
BEGIN 
	EXEC dbo.sp_add_job
		@job_name = N'Client: ; DROP TABLE Task; - Test Task1';							

	EXEC sp_add_jobstep
		@job_name = N'Client: ; DROP TABLE Task; - Test Task1',
		@step_name = N'Process Expired Exclusions',
		@subsystem = N'TSQL',
		@command = N'SELECT ''x''; WAITFOR DELAY ''00:00:05'';', 
		@retry_attempts = 0,
		@retry_interval = 0;

	EXEC dbo.sp_add_jobserver
		@job_name =  N'Client: ; DROP TABLE Task; - Test Task1';
END

Starting Task Order: 1

EXEC msdb.dbo.sp_start_job 'Client: ; DROP TABLE Task; - Test Task1';
Job 'Client: ; DROP TABLE Task; - Test Task1' started successfully.

The next step loop over those job monitoring them for completion status until they all show either success or failure. You will want to consider adding in a logical delay between checks so you aren’t constantly hammering your system.

	--#region - Process batch of jobs
		WHILE EXISTS (SELECT 'x' FROM #Jobs WHERE status is null)
		BEGIN
			SET @id = (SELECT MIN(ID) FROM #Jobs WHERE status is null)
			SET @maxID = (SELECT max(ID) FROM #Jobs WHERE status is null)
												
		--#region - Loop over pending jobs
			WHILE isnull(@id,9999) <= @maxID
			BEGIN
			--#region - null out controls
				SET @JobCompletionStatus = null
				SET @job_name = null
				SET @job_id = null
			--#endregion

			--#region - Get working values
				SET @job_name = (SELECT job_name FROM #Jobs WHERE id = @id)
				SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
				SELECT top 1 @JobCompletionStatus = run_status 
					FROM msdb.dbo.sysjobhistory
					WHERE job_id = @job_id
						and step_id = 0
					ORDER BY run_date desc, run_time desc
			--#endregion

			--#region - SET completed status
				IF @JobCompletionStatus = 1
				BEGIN
					UPDATE #Jobs
					SET status = 1, Running = 0, EndDate = GetDate() 
					WHERE ID = @id
				END
		
				IF @JobCompletionStatus != 1 AND @JobCompletionStatus is not null
				BEGIN
					UPDATE #Jobs
					SET status = 0, Running = 0, EndDate = GetDate() 
					WHERE ID = @id
				END
			--#endregion

			--#region - get next ID to check
				SET @id = (SELECT MIN(ID) FROM #Jobs WHERE status is null and ID > @id)
			--#endregion
			END
		--#endregion
		END
	--#endregion

Then it will delete the jobs based on your parameter choices and success or failure results.

	IF @debug = 1
				select *, [TimeElapsed] = 'Total Time Elapsed: ' + convert(char(8),dateadd(second,datediff(second,StartDate,EndDate),'1900-1-1'),8) from #Jobs

	--#region - Purge Jobs - Uses parameter to keep failures.
		SET @SQL = NULL
		SELECT @SQL = isnull(@SQL,' ') + 'EXEC msdb.dbo.sp_delete_job @job_name = ''' + Job_Name + ''';' 
		FROM #Jobs
		WHERE (@KeepJobOnSuccess = 0 and status = 1)
			OR 
			(@KeepJobOnFailure = 0 AND status = 0)


		IF @debug = 1
			RAISERROR (@sql, 0, 1) WITH NOWAIT

		EXEC sp_EXECuteSQL @SQL;

		IF @debug = 0
			RAISERROR ('Jobs deleted', 0, 1) WITH NOWAIT
	--#endregion

Then it will generate Success/Failure messages and choose whether or not to exit on failure based on your parameter choices. This section will be used to provide custom failure handling.

	--#region - Success/Failure branching
		--#region - Success branch
			SET @Success = NULL
			IF EXISTS (SELECT 'x' FROM #Jobs WHERE status = 1)
			BEGIN
				SELECT @Success = isnull(@Success+', ','') + Job_Name
					FROM #Jobs WHERE status = 1
				SET @msg = 'The folowwing jobs Succeeded: ' + @Success
				
			END
		--#endregion

		--#region - Failure branch
			SET @Failure = NULL
			IF EXISTS (SELECT 'x' FROM #Jobs WHERE status = 0)
			BEGIN
				SELECT @Failure = isnull(@Failure+',','') + Job_Name
					FROM #Jobs WHERE status = 0;

				SET @msg = 'The following jobs Failed: ' + @Failure;
				RAISERROR (@msg, 0, 1) WITH NOWAIT
				
				IF @ExitProcessOnFail = 1
					GOTO FAILURE
			END
		--#endregion
	--#endregion

Complete the outer WHILE loop by marking the relevant tasks completed and setting the next TaskOrder to process.

	--#region - Update those tasks complete
		UPDATE #Tasks
		SET Completed = 1 WHERE TaskOrder = @TaskOrder

		SET @TaskOrder = (SELECT min(TaskOrder) FROM #Tasks WHERE Completed = 0)
		SET @msg = 'New Task Order: ' + Cast(@TaskOrder as varchar) 
		RAISERROR (@msg, 0, 1) WITH NOWAIT
	--#endregion
--#endregion
END
GOTO COMPLETE;

The last bit just has basic placeholders for future custom Success/Failure logic.

FAILURE:
	RAISERROR ('FAILURE. ABORT.', 0, 1) WITH NOWAIT
	
COMPLETE:
	RAISERROR ('DONE', 0, 1) WITH NOWAIT

	SET @msg = 'Total Time Elapsed: ' + convert(char(8),dateadd(second,datediff(second,@Start,GetDate()),'1900-1-1'),8)
	RAISERROR (@msg, 0, 1) WITH NOWAIT

The RunTaskEngine code in its entirety:

IF OBJECT_ID('common.RunTaskEngine') IS NOT NULL
	DROP PROCEDURE common.RunTaskEngine
GO

CREATE PROCEDURE common.RunTaskEngine

		@Client as varchar(250)
	,	@TaskListName as varchar(250) = 'Default'
	,	@DeleteIfJobExists bit = 1
	,	@KeepJobOnFailure bit = 0
	,	@KeepJobOnSuccess bit = 0
	,	@ExitProcessOnFail bit = 1
	
	,	@Tasks varchar(250) = null 

	,	@debug bit = 0
	

AS

SET NOCOUNT ON

/*
	Test1 - Serial
	Test2 - Parallel
	Test3 - Blended
	Test4 - Task Categories
	Test5 - Code + Stored Procs - normal
	Test6 - Code + Stored Procs - Slow
	; DROP TABLE Demo.dbo.MemberProductEligibility; - Serial, SQL Injection
	Fail - Fail
*/

--#region - 'sproc' level parameters
	
--#endregion

--#region - Working parameters
	DECLARE @job_name sysname
	DECLARE @job_id UNIQUEIDENTIFIER
	DECLARE @job_owner sysname
	DECLARE @JobCompletionStatus int
	DECLARE @Start as datetime = GetDate()
	DECLARE @Success as varchar(max)
	DECLARE @Failure as varchar(max)
	DECLARE @SQL as nvarchar(max)
	DECLARE @id as int
	DECLARE @maxID as int
	DECLARE @TaskOrder as int
	DECLARE @maxTaskOrder as int
	DECLARE @TaskID as int
	DECLARE @Code as nvarchar(max)
	DECLARE @msg as varchar(max)
--#endregion

--#region - Control Temp Table
	IF OBJECT_ID('TempDB..#Tasks') IS NOT NULL
		DROP TABLE #Tasks

	CREATE TABLE #Tasks
	(
			ID int identity(1,1)
		,	TaskName varchar(100)
		,	Code varchar(max)
		,	TaskOrder int
		,	Completed bit 
	)
--#endregion

--#region - Load logic
	INSERT INTO #Tasks(TaskName, Code, TaskOrder,Completed)

	SELECT	t.TaskName
		,	CASE 
				WHEN t.TaskTypeID = 1 THEN '
				DECLARE @SQL as nvarchar(max)
				DECLARE @Client as varchar(50) = ''''' + @Client + '''''
				
				SET @SQL = N''''EXEC Demo.common.TestSproc @Client = @Client''''

				EXEC SP_ExecuteSQL @SQL
				, N''''@Client varchar(50)''''
				, @Client = @Client 
				'
				WHEN t.TaskTypeID = 2 THEN REPLACE(t.Code,'''','''''')
				ELSE ''
			END
		,	tl.TaskOrder
		,	0 
	FROM Demo.dbo.TaskList tl 
		INNER JOIN Demo.dbo.Task t 
			ON t.TaskID = tl.TaskID

	WHERE tl.Client = @Client
		AND tl.TaskListName = @TaskListName
		AND (
			@Tasks IS NULL
			OR
			t.TaskID IN (SELECT [data] FROM UDFs.dbo.Split(@Tasks,','))
			)
		
	order by tl.TaskOrder
--#endregion

SET @TaskOrder = (SELECT MIN(TaskOrder) FROM #Tasks WHERE Completed = 0)
SET @MaxTaskOrder = (SELECT MAX(TaskOrder) FROM #Tasks WHERE Completed = 0)

IF @debug = 1
	select * from #tasks

SET @msg = 'Starting Task Order: ' + Cast(@TaskOrder AS VARCHAR) 
RAISERROR (@msg, 0, 1) WITH NOWAIT

WHILE @TaskOrder <= @MaxTaskOrder
BEGIN
	--#region - Working Temp Tables
		IF OBJECT_ID('TempDB..#Jobs') IS NOT NULL
				DROP TABLE #Jobs

			CREATE TABLE #Jobs 
			(
					ID int identity(1,1)
				,	job_name varchar(250)
				,	code nvarchar(max)
				,	running bit
				,	status bit
				,	created bit
				,	StartDate datetime2(3)
				,	EndDate datetime2(3)
			)
	--#endregion

	--#region - Load jobs
		INSERT INTO #Jobs
		SELECT 
		'Client: ' + @Client + ' - ' + TaskName, Code, 0,null,0,GetDate(),null FROM #Tasks t
		WHERE TaskOrder = @TaskOrder
				
	--#endregion

	--#region - Create Jobs
		WHILE EXISTS(SELECT 1 FROM #Jobs WHERE created = 0)
		BEGIN	
				
			SET @TaskID = (SELECT TOP 1 ID FROM #Jobs WHERE created = 0)
			SET @job_name = (SELECT TOP 1 job_name FROM #Jobs WHERE ID = @TaskID)
			SET @code = (SELECT TOP 1 code FROM #Jobs WHERE ID = @TaskID)

		--#region - Delete Job If It Exists (parameter based)
			IF @DeleteIfJobExists = 1 AND EXISTS (SELECT 'x' FROM msdb.dbo.sysjobs_view job WHERE [name] = @job_name)
			BEGIN
				EXEC msdb.dbo.sp_delete_job @job_name = @Job_name 
			END
		--#endregion

			SET @SQL = 
			REPLACE(REPLACE(N'
				USE msdb;

				IF NOT EXISTS (SELECT ''x'' FROM msdb.dbo.sysjobs WHERE [name] = N''{JobName}'')
				BEGIN 
					EXEC dbo.sp_add_job
						@job_name = N''{JobName}'';							

					EXEC sp_add_jobstep
						@job_name = N''{JobName}'',
						@step_name = N''Process Expired Exclusions'',
						@subsystem = N''TSQL'',
						@command = N''{Code}'', 
						@retry_attempts = 0,
						@retry_interval = 0;

					EXEC dbo.sp_add_jobserver
						@job_name =  N''{JobName}'';
				END'
				,'{JobName}',@job_name)
				,'{Code}',@Code)

			IF @debug = 1
				RAISERROR (@SQL, 0, 1) WITH NOWAIT

			EXEC SP_EXECuteSql @SQL
			
			IF @debug = 0
				SET @msg = 'Job Created - ' + @job_name
				RAISERROR (@msg, 0, 1) WITH NOWAIT

			UPDATE #Jobs SET created = 1 WHERE ID = @TaskID
		END
	--#endregion

	--#region - Start Jobs
		SET @SQL = null;
		SELECT @SQL = ISNULL(@SQL,' ') + 'EXEC msdb.dbo.sp_start_job ''' + job_name + ''';'
		FROM #Jobs
		
		
		IF @debug = 1 
			RAISERROR (@sql, 0, 1) WITH NOWAIT

		IF @debug = 0 
			RAISERROR ('Starting jobs.', 0, 1) WITH NOWAIT
			
		EXEC sp_EXECuteSQL @SQL;
	--#endregion

	--#region - Process batch of jobs
		WHILE EXISTS (SELECT 'x' FROM #Jobs WHERE status is null)
		BEGIN
			SET @id = (SELECT MIN(ID) FROM #Jobs WHERE status is null)
			SET @maxID = (SELECT max(ID) FROM #Jobs WHERE status is null)
												
		--#region - Loop over pending jobs
			WHILE isnull(@id,9999) <= @maxID
			BEGIN
			--#region - null out controls
				SET @JobCompletionStatus = null
				SET @job_name = null
				SET @job_id = null
			--#endregion

			--#region - Get working values
				SET @job_name = (SELECT job_name FROM #Jobs WHERE id = @id)
				SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
				SELECT top 1 @JobCompletionStatus = run_status 
					FROM msdb.dbo.sysjobhistory
					WHERE job_id = @job_id
						and step_id = 0
					ORDER BY run_date desc, run_time desc
			--#endregion

			--#region - SET completed status
				IF @JobCompletionStatus = 1
				BEGIN
					UPDATE #Jobs
					SET status = 1, Running = 0, EndDate = GetDate() 
					WHERE ID = @id
				END
		
				IF @JobCompletionStatus != 1 AND @JobCompletionStatus is not null
				BEGIN
					UPDATE #Jobs
					SET status = 0, Running = 0, EndDate = GetDate() 
					WHERE ID = @id
				END
			--#endregion

			--#region - get next ID to check
				SET @id = (SELECT MIN(ID) FROM #Jobs WHERE status is null and ID > @id)
			--#endregion
			END
		--#endregion
		END
	--#endregion
		IF @debug = 1
				select *, [TimeElapsed] = 'Total Time Elapsed: ' + convert(char(8),dateadd(second,datediff(second,StartDate,EndDate),'1900-1-1'),8) from #Jobs

	--#region - Purge Jobs - Uses parameter to keep failures.
		SET @SQL = NULL
		SELECT @SQL = isnull(@SQL,' ') + 'EXEC msdb.dbo.sp_delete_job @job_name = ''' + Job_Name + ''';' 
		FROM #Jobs
		WHERE (@KeepJobOnSuccess = 0 and status = 1)
			OR 
			(@KeepJobOnFailure = 0 AND status = 0)


		IF @debug = 1
			RAISERROR (@sql, 0, 1) WITH NOWAIT

		EXEC sp_EXECuteSQL @SQL;

		IF @debug = 0
			RAISERROR ('Jobs deleted', 0, 1) WITH NOWAIT
	--#endregion

	--#region - Success/Failure branching
		--#region - Success branch
			SET @Success = NULL
			IF EXISTS (SELECT 'x' FROM #Jobs WHERE status = 1)
			BEGIN
				SELECT @Success = isnull(@Success+', ','') + Job_Name
					FROM #Jobs WHERE status = 1
				SET @msg = 'The folowwing jobs Succeeded: ' + @Success
				
			END
		--#endregion

		--#region - Failure branch
			SET @Failure = NULL
			IF EXISTS (SELECT 'x' FROM #Jobs WHERE status = 0)
			BEGIN
				SELECT @Failure = isnull(@Failure+',','') + Job_Name
					FROM #Jobs WHERE status = 0;

				SET @msg = 'The following jobs Failed: ' + @Failure;
				RAISERROR (@msg, 0, 1) WITH NOWAIT
				
				IF @ExitProcessOnFail = 1
					GOTO FAILURE
			END
		--#endregion
	--#endregion

	--#region - Update those tasks complete
		UPDATE #Tasks
		SET Completed = 1 WHERE TaskOrder = @TaskOrder

		SET @TaskOrder = (SELECT min(TaskOrder) FROM #Tasks WHERE Completed = 0)
		SET @msg = 'New Task Order: ' + Cast(@TaskOrder as varchar) 
		RAISERROR (@msg, 0, 1) WITH NOWAIT
	--#endregion
--#endregion
END
GOTO COMPLETE;

FAILURE:
	RAISERROR ('FAILURE. ABORT.', 0, 1) WITH NOWAIT
	
COMPLETE:
	RAISERROR ('DONE', 0, 1) WITH NOWAIT

	SET @msg = 'Total Time Elapsed: ' + convert(char(8),dateadd(second,datediff(second,@Start,GetDate()),'1900-1-1'),8)
	RAISERROR (@msg, 0, 1) WITH NOWAIT
--#endregion

The Results

Once you have run the set up script for both the tasks and the task engine here are some of the baseline things to play around with:

exec Demo.common.RunTaskEngine 'Test1' -- Fully Serial
exec Demo.common.RunTaskEngine 'Test2' -- Fully Parallel
exec Demo.common.RunTaskEngine 'Test3' -- Blended

exec Demo.common.RunTaskEngine 'Test5' -- Mix of Code and Stored Proc, regular speed due to sproc logic
exec Demo.common.RunTaskEngine 'Test6' -- Mix of Code and Stored Proc, slower speed due to sproc logic

exec Demo.common.RunTaskEngine '; DROP TABLE Task;' -- Why we build out via sp_executeSQL instead of just string concatenation

exec Demo.common.RunTaskEngine 'Fail' -- Failure
	, @KeepJobOnFailure = 0
	, @ExitProcessOnFail = 1

While this is still a work in progress, I am excited by the potential I’ve seen so far and I am looking forward to utilizing this technique in future projects to help flow between serial and parallel processing where appropriate.

You can download all the scripts required to play around with this demo HERE.

Categorised in: ,

Leave a Reply

Your email address will not be published.

Subscribe

Want occasional notes from me?
(I promise, no spam.)

* indicates required

How did I not already know this?

Dirty hands

Quick and Dirty

Nest

The Curious Case of the Nested REPLACE()

© Copyright 2024 The Data You Need. All rights reserved. Privacy Policy Cookie Policy