אפר11
Written by:
ronen ariely
11/04/2020 01:41 
Good day guys,
I hope you are all safe locked at your home spending the time to learn and improve yourself.
I am using a lot of time in helping others in the forums, especially in Microsoft MSDN and TechNet forums, but not limited to these, and today question came from stackoverflow (an interface which I really hate but try to visit from time to time and help people).
The goal for this post is to Execute multiple jobs Synchronously from inside a main job, using only the SQL Server Agent pure solutions.
Before I start, I want to say that the reason I emphasize that I focus only on SQL Server Agent pure solutions, is that these are not the solutions which I will probably use in production if I have free privilege to design the solution, but these are the requirements for our solutions today.
The problem
Execute a job from inside another job is a simple task like executing any query. We simply need to start the external Job using the built-in stored procedure msdb.dbo.sp_start_job. Unfortunately if we have multiple jobs which needed to be executed in specific order Synchronously (meaning that only when one job end then the next one start), is a more complex task using the built-in SQL Server Agent. In this post I will mentioned several options to solve this task.
Demo problem
I will create a table to store the times which each job is executed.
USE [msdb]
GO
CREATE table T(
job nvarchar(100),
Step nvarchar(100),
ExcTime DATETIME2
)
GO
Creating first Job
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Job1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Job1'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Job1', @step_name=N'Job1Step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
INSERT msdb.dbo.T(job,Step,ExcTime) values (''job1Start'', ''step1Start'', SYSDATETIME());
WAITFOR DELAY ''00:00:10'';
INSERT msdb.dbo.T(job,Step,ExcTime) values (''job1End'', ''step1End'', SYSDATETIME());
',
@database_name=N'master',
@flags=0
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Job1',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@notify_email_operator_name=N'',
@notify_page_operator_name=N''
GO
-- test the job:
-- I clean the data in the table ->
-- execute the job which will insert data ->
-- watch the data after 15 seconds so the job will finish
TRUNCATE TABLE msdb.dbo.T
GO
EXEC dbo.sp_start_job N'Job1' ;
GO
WAITFOR DELAY '00:00:15'
GO
select * from msdb.dbo.T order by ExcTime
GO
---------------------------------------------
Creating second Job
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Job2',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Job2'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Job2', @step_name=N'Job2Step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
INSERT msdb.dbo.T(job,Step,ExcTime) values (''Job2Start'', ''step1Start'', SYSDATETIME());
WAITFOR DELAY ''00:00:10'';
INSERT msdb.dbo.T(job,Step,ExcTime) values (''Job2End'', ''step1End'', SYSDATETIME());
',
@database_name=N'master',
@flags=0
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Job2',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@notify_email_operator_name=N'',
@notify_page_operator_name=N''
GO
-- test the job:
-- I clean the data in the table ->
-- execute the job which will insert data ->
-- watch the data after 15 seconds so the job will finish
EXEC dbo.sp_start_job N'Job2' ;
GO
WAITFOR DELAY '00:00:15'
GO
select * from msdb.dbo.T order by ExcTime
GO -- OK we should see two rows from previous test and two new rows
---------------------------------------------
OK, now we can clean the table and show the problem, by executing the two jobs one after the other
USE [msdb]
GO
TRUNCATE TABLE msdb.dbo.T
GO
EXEC dbo.sp_start_job N'Job1' ;
GO
EXEC dbo.sp_start_job N'Job2' ;
GO
-- wait for 25 secons and check the data
WAITFOR DELAY '00:00:25'
select * from msdb.dbo.T order by ExcTime
GO
/*
job Step ExcTime
Job2Start step1Start 2020-04-11 03:17:41.9550418
job1Start step1Start 2020-04-11 03:17:41.9550418
Job2End step1End 2020-04-11 03:17:51.9579893
job1End step1End 2020-04-11 03:17:51.9579893
*/
------- NOTICE! Jobs run asynchronously
The problem for our requirement is that SQL Server Agent is designed for best performance and for this it is designed to execute jobs in the background Asynchronously.
Solution one: Executing each job from the previous job
The basic approach is to Start the first job, and inside each Job at the end of the code add command (using sp_start_job) to execute the next job. Since the command comes at the end it will execute after the rest of the code assuming the rest of the code is simple query which run synchronously.
The disadvantage of this solution is that we need to change the code of the jobs in order to execute the next job. This means that if we have general jobs which we want to execute from time to time separately and sometimes together one after the other synchronously, then this solution does not fit.
Using this approach we must start by creating the last job which we need to execute since the job that need to run before it should call this job and therefore, we want it ready in the database. Therefore, we create the second job first
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'RonenJob2',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'RonenJob2'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'RonenJob2', @step_name=N'RonenJob2Step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
INSERT msdb.dbo.T(job,Step,ExcTime) values (''RonenJob2Start'', ''step1Start'', SYSDATETIME());
WAITFOR DELAY ''00:00:10'';
INSERT msdb.dbo.T(job,Step,ExcTime) values (''RonenJob2End'', ''step1End'', SYSDATETIME());
WAITFOR DELAY ''00:00:01'';
',
@database_name=N'master',
@flags=0
GO
EXEC msdb.dbo.sp_update_job @job_name=N'RonenJob2',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@notify_email_operator_name=N'',
@notify_page_operator_name=N''
GO
Now let's create the first Job which will execute the second one in second step
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'RonenJob1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'pituach-w10-vm\ronen', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'RonenJob1', @server_name = N'PITUACH-W10-VM\SQL2019'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'RonenJob1', @step_name=N'job1step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT msdb.dbo.T(job,Step,ExcTime) values (''Ronenjob1Start'', ''step1Start'', SYSDATETIME());
WAITFOR DELAY ''00:00:10'';
INSERT msdb.dbo.T(job,Step,ExcTime) values (''Ronenjob1End'', ''step1End'', SYSDATETIME());
WAITFOR DELAY ''00:00:01'';',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'RonenJob1', @step_name=N'job1step2',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_start_job N''RonenJob2'';',
--@command=N'INSERT msdb.dbo.T(job,Step,ExcTime) values (''-----'', ''------'', SYSDATETIME());',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'RonenJob1',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'pituach-w10-vm\ronen',
@notify_email_operator_name=N'',
@notify_page_operator_name=N''
GO
And here is the result as expected:
TRUNCATE TABLE msdb.dbo.T
GO
EXEC dbo.sp_start_job N'RonenJob1' ;
GO
WAITFOR DELAY '00:00:25'
select * from msdb.dbo.T order by ExcTime
GO
Solution two: Using loop to check if the previous job finished
In this solution we use two unrelated jobs. The solution will be bases on the execution procedure, which will include a loop to check if the first Job finished. Let's clean the table first
TRUNCATE TABLE msdb.dbo.T
GO
For the sake of this solution we will use the first two jobs which we created in order to show the problem. The difference will be in the execution.
declare @I INT
SET @I = 0;
EXEC dbo.sp_start_job N'Job1';
WAITFOR DELAY '00:00:02';
---------------------------------- Wait for job 1 to end
while exists (
select * from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
where name = 'Job1' and stop_execution_date is null and start_execution_date is not null
)
begin
SET @I = @I + 1;
PRINT CONVERT(NVARCHAR(10), @I ) + '. Waiting Job1...'
WAITFOR DELAY '00:00:02';
end
---------------------------------- Execute Job2
SET @I = 0;
EXEC dbo.sp_start_job N'Job2';
WAITFOR DELAY '00:00:02';
---------------------------------- Wait for job 2 to end
while exists (
select * from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
where name = 'Job2' and stop_execution_date is null and start_execution_date is not null
)
begin
SET @I = @I + 1;
PRINT CONVERT(NVARCHAR(10), @I ) + '. Waiting Job2...'
WAITFOR DELAY '00:00:02';
end
PRINT 'END'
And here we can check the table and confirm that the jobs were executed one after the other
-- wait for 25 secons and check the data
WAITFOR DELAY '00:00:25'
select * from msdb.dbo.T order by ExcTime
GO
Option three: Executing the jobs one after the other through one of the embedded languages
Executing the jobs one after the other through one of the embedded languages and technologies which SQL Server provides, like SQLCLR, VBS script using Distributed Management Objects (DMO), Python, and so on... This might be crazy and probably not recommended for most cases but it is a practical option for some scenarios.
Walla...
One more Corona Day pass and we can go to sleep 😀