Replicating Jobs Between SQL Instances – Jobs
This is part 5 in the series of how to replicate SQL Jobs between instances and we’re finally up to the part that actually replicates the jobs. To get the background and all the steps you need to do before this one, go and read the other posts first.
Replicating Jobs has taken a lot of trial and error work (and a lot of alerts to the rest of the team that the job was failing) before I got it right. There are a number of scenarios that we have to deal with:
- A brand new job
- A new step in an existing job
- A deleted step from an existing job
- A change in the job description
- A change in the job step
- Probably something else
As mentioned in a previous post, we’re not going to automatically delete jobs off secondary servers.
There is a lot of code to do a few simple things. Basically we’re going to get a list of jobs that need inserting or updating, and then cursor through all the jobs and their steps and insert/update/delete(steps) as required.
1. Setup
This is the same as the setup for categories. The script checks that it isn’t running on the primary server, creates a variable with the name of the primary server and creates a temp table.
SET NOCOUNT ON
IF 0 = (
SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName')
)
BEGIN
DECLARE @PartnerServer SYSNAME = 'PrimaryReplicaName'
DECLARE @Machine SYSNAME
DECLARE @SQL NVARCHAR(MAX)
IF CharIndex('', @PartnerServer) > 0
BEGIN
SET @Machine = LEFT(@PartnerServer, CharIndex('', @PartnerServer) - 1);
END
ELSE
BEGIN
SET @Machine = @PartnerServer;
END
CREATE TABLE #Jobs (
job_id UNIQUEIDENTIFIER
,Action VARCHAR(6)
,[originating_server_id] [int] NOT NULL
,[name] [sysname] NOT NULL
,[enabled] [tinyint] NOT NULL
,[description] [nvarchar](512) NULL
,[start_step_id] [int] NOT NULL
,[category_id] [int] NOT NULL
,[categoryname] SYSNAME NULL
,[owner_sid] [varbinary](85) NOT NULL
,[owner_name] SYSNAME NULL
,[notify_level_eventlog] [int] NOT NULL
,[notify_level_email] [int] NOT NULL
,[notify_level_netsend] [int] NOT NULL
,[notify_level_page] [int] NOT NULL
,[notify_email_operator_id] [int] NOT NULL
,[notify_netsend_operator_id] [int] NOT NULL
,[notify_page_operator_id] [int] NOT NULL
,[Notify_Email_Operator_name] SYSNAME NULL
,[delete_level] [int] NOT NULL
,[date_created] [datetime] NOT NULL
,[date_modified] [datetime] NOT NULL
,[version_number] [int] NOT NULL
)
CREATE TABLE #JobSteps (
[step_id] [int] NOT NULL
,[step_name] [sysname] NOT NULL
,[subsystem] [nvarchar](40) NOT NULL
,[command] [nvarchar](max) NULL
,[additional_parameters] [nvarchar](max) NULL
,[cmdexec_success_code] [int] NOT NULL
,[on_success_action] [tinyint] NOT NULL
,[on_success_step_id] [int] NULL
,[on_fail_action] [tinyint] NULL
,[on_fail_step_id] [int] NULL
,[server] [sysname] NULL
,[database_name] [sysname] NULL
,[database_user_name] [sysname] NULL
,[retry_attempts] [int] NULL
,[retry_interval] [int] NULL
,[os_run_priority] [int] NULL
,[output_file_name] [nvarchar](200) NULL
,[flags] [int] NULL
,[proxy_id] [int] NULL
)
--Do Update Jobs
DROP TABLE #jobSteps
DROP TABLE #Jobs
END
A bit more to setup than other jobs, but still pretty familiar if you’ve been following along. This time 2 temp tables – one for Jobs and one for Steps.
2. Find
Again, pretty similar to the others, just more complex.
SET @SQL = CAST(
'
SELECT *
FROM
(
SELECT primaryMSDB.Job_id
,CASE WHEN msdbLocal.Job_id IS NULL THEN ''INSERT''
WHEN msdbLocal.Job_id IS NOT NULL THEN ''UPDATE''
ELSE NULL
END as Action
,primaryMSDB.[originating_server_id]
,primaryMSDB.[name]
,primaryMSDB.[enabled]
,primaryMSDB.[description]
,primaryMSDB.[start_step_id]
,primaryMSDB.[category_id]
,primaryCategories.name as categoryName
,primaryMSDB.[owner_sid]
,CASE WHEN primaryMSDB.[owner_sid] = 0x01 THEN ''sa'' ELSE primaryPrincipals.name END ownerName
,primaryMSDB.[notify_level_eventlog]
,primaryMSDB.[notify_level_email]
,primaryMSDB.[notify_level_netsend]
,primaryMSDB.[notify_level_page]
,primaryMSDB.[notify_email_operator_id]
,primaryMSDB.[notify_netsend_operator_id]
,primaryMSDB.[notify_page_operator_id]
,sysoperators.name notifyEmailName
,primaryMSDB.[delete_level]
,primaryMSDB.[date_created]
,primaryMSDB.[date_modified]
,primaryMSDB.[version_number]
FROM '
+ QUOTENAME(@Machine) + '.msdb.dbo.sysJobs as primaryMSDB
LEFT JOIN ' + QUOTENAME(@Machine) + '.msdb.dbo.sysCategories as primaryCategories
ON primaryCategories.category_id = primaryMSDB.category_id
LEFT JOIN ' + QUOTENAME(@Machine) + '.msdb.dbo.sysoperators
ON sysoperators.id = primaryMSDB.notify_email_operator_id
LEFT JOIN msdb.dbo.sysJobs msdbLocal
ON primaryMSDB.name = msdbLocal.name
LEFT JOIN ' + QUOTENAME(@Machine) + '.master.sys.database_principals as primaryPrincipals
ON primaryPrincipals.sid = primaryMSDB.[owner_sid]
) InsertUpdate
WHERE Action IS NOT NULL
' AS VARCHAR(MAX))
INSERT INTO #Jobs
EXEC sp_ExecuteSQL @SQL
Grab all the jobs that need updating into the #Jobs table. Notice that we are joining based on the name of the job – this assumes that we won’t rename jobs. If we rename a job then we need to delete the original from the secondary servers. For our use case we were happy with this compromise. Also note that all jobs on the primary are going to be selected. There is a case for action being null, but in reality I don’t think it ever can be. We want every job to be included because at this stage we haven’t checked if it has any steps that are different.
3. Copy
Like the others, we’re going to cursor through the records, find what needs updating or inserting and update/insert the job header by calling sp_update_job or sp_add_job
DECLARE @originalJobID UNIQUEIDENTIFIER
,@targetJobID UNIQUEIDENTIFIER
,@name [sysname]
,@enabled [tinyint]
,@description [nvarchar] (512)
,@start_step_id [int]
,@category_id [int]
,@owner_sid VARBINARY(85)
,@ownerName SYSNAME
,@notify_level_eventlog [int]
,@notify_level_email [int]
,@notify_level_netsend [int]
,@notify_level_page [int]
,@notify_email_operator_id [int]
,@notify_netsend_operator_id [int]
,@notify_page_operator_id [int]
,@email_operator_name SYSNAME
,@delete_level [int]
,@date_created [datetime]
,@date_modified [datetime]
,@version_number [int]
,@Action SYSNAME
,@CategoryName SYSNAME
DECLARE db_cursor CURSOR
FOR
SELECT job_id
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[categoryname]
,[owner_sid]
,[owner_name]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[Notify_Email_Operator_name]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,Action
FROM #Jobs
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @originalJobID
,@name
,@enabled
,@description
,@start_step_id
,@category_id
,@CategoryName
,@owner_sid
,@ownerName
,@notify_level_eventlog
,@notify_level_email
,@notify_level_netsend
,@notify_level_page
,@notify_email_operator_id
,@notify_netsend_operator_id
,@notify_page_operator_id
,@email_operator_name
,@delete_level
,@date_created
,@date_modified
,@version_number
,@Action
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Action = 'Update'
BEGIN
print 'Updating Job ' + @name
EXEC msdb.dbo.sp_update_job @job_name = @name
,@enabled = @enabled
,@description = @description
,@start_step_id = @start_step_id
,@category_name = @CategoryName
,@owner_login_Name = @ownerName
,@notify_level_eventlog = @notify_level_eventlog
,@notify_level_email = @notify_level_email
,@notify_level_netsend = @notify_level_netsend
,@notify_level_page = @notify_level_page
,@notify_email_operator_name = @email_operator_name
,@delete_level = @delete_level
SELECT @targetJobID = sysjobs.job_id
FROM msdb.dbo.sysjobs
WHERE NAME = @name
END
IF @Action = 'INSERT'
BEGIN
set @targetJobID = null
print 'Creating Job ' + @name
print 'Owner: ' + @ownerName
EXEC msdb.dbo.sp_add_job @job_name = @name
,@enabled = @enabled
,@description = @description
,@start_step_id = @start_step_id
,@category_name = @CategoryName
,@owner_login_Name = @ownerName
,@notify_level_eventlog = @notify_level_eventlog
,@notify_level_email = @notify_level_email
,@notify_level_netsend = @notify_level_netsend
,@notify_level_page = @notify_level_page
,@notify_email_operator_name = @email_operator_name
,@delete_level = @delete_level
,@job_ID = @targetJobID OUTPUT
EXEC msdb.dbo.sp_add_jobserver @job_id=@targetJobID, @server_name = @@SERVERNAME
print @targetJobID
print 'Job Created'
END
Of significance, and the SQL command I was missing for a long time, was line 123 (highlighted above) that calls sp_add_jobserver. The job inserts fine and runs fine without this step BUT you cannot schedule the job (next blog post) until it has been added to the server. This was a pain to find and I probably should have found it sooner. It would have been nice if sp_add_job included the option to set the jobserver, but it doesn’t.
Once the job headers are copied and we have set @targetJobID we can move on to the steps. The following is a lot of code, but it’s pretty straight forward. We are getting a list of steps, and working out what needs to be added/updated/deleted from the local job. The way this works is on StepID, so in the scenario that you delete say step 2 from a 5 step job on the primary, the secondary will update step 2 with step 3, step 3 with step 4, step 4 with step 5 and delete step 5. It gives you the right steps at the end, but if you keep track of individual steps then you’ll need to work something else out.
DECLARE @step_id [int]
,@step_name [sysname]
,@subsystem [nvarchar] (40)
,@command [nvarchar] (max)
,@flags [int]
,@additional_parameters [nvarchar] (max)
,@cmdexec_success_code [int]
,@on_success_action [tinyint]
,@on_success_step_id [int]
,@on_fail_action [tinyint]
,@on_fail_step_id [int]
,@server [sysname]
,@database_name [sysname]
,@database_user_name [sysname]
,@retry_attempts [int]
,@retry_interval [int]
,@os_run_priority [int]
,@output_file_name [nvarchar] (200)
,@proxy_id [int]
SET @SQL = 'SELECT step_id,
step_name,
subsystem,
command,
additional_parameters,
cmdexec_success_code,
on_success_action,
on_success_step_id,
on_fail_action,
on_fail_step_id,
server,
database_name,
database_user_name,
retry_attempts,
retry_interval,
os_run_priority,
output_file_name,
flags,
proxy_id
FROM ' + QUOTENAME(@Machine) + '.msdb.dbo.sysjobsteps
WHERE job_id = ''' + CAST(@originalJobID AS VARCHAR(100)) + ''''
DELETE
FROM #JobSteps
INSERT INTO #JobSteps
EXEC sp_ExecuteSQL @SQL
--select * From #JobSteps
IF (
SELECT COUNT(1)
from #JobSteps
LEFT JOIN msdb.dbo.sysjobsteps
ON sysjobsteps.job_id = @targetJobID
AND sysjobsteps.step_id = #JobSteps.step_id
AND sysjobsteps.step_name = #JobSteps.step_name
WHERE sysjobsteps.step_id IS NULL
) > 0
OR
(
SELECT COUNT(1)
from msdb.dbo.sysjobsteps
LEFT JOIN #JobSteps
ON sysjobsteps.step_id = #JobSteps.step_id
AND sysjobsteps.step_name = #JobSteps.step_name
WHERE sysjobsteps.step_id IS NULL
AND sysjobsteps.job_id = @targetJobID
) > 0
BEGIN
DECLARE db_jobStep_todelete_cursor CURSOR
FOR
SELECT step_id
FROM msdb.dbo.sysjobsteps
WHERE sysjobsteps.job_id = @targetJobID
order by step_id desc
OPEN db_jobStep_todelete_cursor
FETCH NEXT
FROM db_jobStep_todelete_cursor
INTO @step_id
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Deleting Step ' + cast(@step_id as varchar(5)) + ' from job ' + @name
EXEC msdb.dbo.sp_delete_jobstep @job_id = @targetJobID
,@step_id = @step_id
FETCH NEXT
FROM db_jobStep_todelete_cursor
INTO @step_id
END
CLOSE db_jobStep_todelete_cursor
DEALLOCATE db_jobStep_todelete_cursor
END
---------------------------
--cursor through all steps for the job and update steps...just do the update
DECLARE db_jobStep_cursor CURSOR
FOR
SELECT step_id
,step_name
,subsystem
,command
,flags
,additional_parameters
,cmdexec_success_code
,on_success_action
,on_success_step_id
,on_fail_action
,on_fail_step_id
,SERVER
,database_name
,database_user_name
,retry_attempts
,retry_interval
,os_run_priority
,output_file_name
,proxy_id
FROM #JobSteps
order by step_id
OPEN db_jobStep_cursor
FETCH NEXT
FROM db_jobStep_cursor
INTO @step_id
,@step_name
,@subsystem
,@command
,@flags
,@additional_parameters
,@cmdexec_success_code
,@on_success_action
,@on_success_step_id
,@on_fail_action
,@on_fail_step_id
,@server
,@database_name
,@database_user_name
,@retry_attempts
,@retry_interval
,@os_run_priority
,@output_file_name
,@proxy_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (
SELECT 1
FROM msdb.dbo.sysjobsteps
WHERE job_id = @targetJobID
AND step_id = @step_id
)
BEGIN
EXEC msdb.dbo.sp_update_jobstep @job_id = @targetJobID
,@step_id = @step_id
,@subsystem = @subsystem
,@command = @command
,@flags = @flags
,@additional_parameters = @additional_parameters
,@cmdexec_success_code = @cmdexec_success_code
,@on_success_action = @on_success_action
,@on_success_step_id = @on_success_step_id
,@on_fail_action = @on_fail_action
,@on_fail_step_id = @on_fail_step_id
,@server = @server
,@database_name = @database_name
,@database_user_name = @database_user_name
,@retry_attempts = @retry_attempts
,@retry_interval = @retry_interval
,@os_run_priority = @os_run_priority
,@output_file_name = @output_file_name
,@proxy_id = @proxy_id
END
ELSE
BEGIN
print 'creating' + @step_name
EXEC msdb.dbo.sp_add_jobstep @job_id = @targetJobID
,@step_id = @step_id
,@step_name = @step_name
,@subsystem = @subsystem
,@command = @command
,@flags = @flags
,@additional_parameters = @additional_parameters
,@cmdexec_success_code = @cmdexec_success_code
,@on_success_action = @on_success_action
,@on_success_step_id = @on_success_step_id
,@on_fail_action = @on_fail_action
,@on_fail_step_id = @on_fail_step_id
,@server = @server
,@database_name = @database_name
,@database_user_name = @database_user_name
,@retry_attempts = @retry_attempts
,@retry_interval = @retry_interval
,@os_run_priority = @os_run_priority
,@output_file_name = @output_file_name
,@proxy_id = @proxy_id
END
FETCH NEXT
FROM db_jobStep_cursor
INTO @step_id
,@step_name
,@subsystem
,@command
,@flags
,@additional_parameters
,@cmdexec_success_code
,@on_success_action
,@on_success_step_id
,@on_fail_action
,@on_fail_step_id
,@server
,@database_name
,@database_user_name
,@retry_attempts
,@retry_interval
,@os_run_priority
,@output_file_name
,@proxy_id
END
CLOSE db_jobStep_cursor
DEALLOCATE db_jobStep_cursor
And 520 lines of SQL later, that’s it! The whole step is posted below. As always, try out code before you use it on your production servers just to make sure it works for your conditions.
This is the hardest part of the replication. The next 2 steps are to join the job to the schedules and to disable jobs that we don’t want running (SSRS jobs).
SET NOCOUNT ON
IF 0 = (
SELECT master.dbo.fCheckIfPrimary('SQLAAG_ENT_PROD')
)
BEGIN
DECLARE @PartnerServer SYSNAME = 'SQLAAG_ENT_PROD'
DECLARE @Machine SYSNAME
DECLARE @SQL NVARCHAR(MAX)
IF CharIndex('', @PartnerServer) > 0
BEGIN
SET @Machine = LEFT(@PartnerServer, CharIndex('', @PartnerServer) - 1);
END
ELSE
BEGIN
SET @Machine = @PartnerServer;
END
CREATE TABLE #Jobs (
job_id UNIQUEIDENTIFIER
,Action VARCHAR(6)
,[originating_server_id] [int] NOT NULL
,[name] [sysname] NOT NULL
,[enabled] [tinyint] NOT NULL
,[description] [nvarchar](512) NULL
,[start_step_id] [int] NOT NULL
,[category_id] [int] NOT NULL
,[categoryname] SYSNAME NULL
,[owner_sid] [varbinary](85) NOT NULL
,[owner_name] SYSNAME NULL
,[notify_level_eventlog] [int] NOT NULL
,[notify_level_email] [int] NOT NULL
,[notify_level_netsend] [int] NOT NULL
,[notify_level_page] [int] NOT NULL
,[notify_email_operator_id] [int] NOT NULL
,[notify_netsend_operator_id] [int] NOT NULL
,[notify_page_operator_id] [int] NOT NULL
,[Notify_Email_Operator_name] SYSNAME NULL
,[delete_level] [int] NOT NULL
,[date_created] [datetime] NOT NULL
,[date_modified] [datetime] NOT NULL
,[version_number] [int] NOT NULL
)
CREATE TABLE #JobSteps (
[step_id] [int] NOT NULL
,[step_name] [sysname] NOT NULL
,[subsystem] [nvarchar](40) NOT NULL
,[command] [nvarchar](max) NULL
,[additional_parameters] [nvarchar](max) NULL
,[cmdexec_success_code] [int] NOT NULL
,[on_success_action] [tinyint] NOT NULL
,[on_success_step_id] [int] NULL
,[on_fail_action] [tinyint] NULL
,[on_fail_step_id] [int] NULL
,[server] [sysname] NULL
,[database_name] [sysname] NULL
,[database_user_name] [sysname] NULL
,[retry_attempts] [int] NULL
,[retry_interval] [int] NULL
,[os_run_priority] [int] NULL
,[output_file_name] [nvarchar](200) NULL
,[flags] [int] NULL
,[proxy_id] [int] NULL
)
SET @SQL = CAST(
'
SELECT *
FROM
(
SELECT primaryMSDB.Job_id
,CASE WHEN msdbLocal.Job_id IS NULL THEN ''INSERT''
WHEN msdbLocal.Job_id IS NOT NULL THEN ''UPDATE''
ELSE NULL
END as Action
,primaryMSDB.[originating_server_id]
,primaryMSDB.[name]
,primaryMSDB.[enabled]
,primaryMSDB.[description]
,primaryMSDB.[start_step_id]
,primaryMSDB.[category_id]
,primaryCategories.name as categoryName
,primaryMSDB.[owner_sid]
,CASE WHEN primaryMSDB.[owner_sid] = 0x01 THEN ''sa'' ELSE primaryPrincipals.name END ownerName
,primaryMSDB.[notify_level_eventlog]
,primaryMSDB.[notify_level_email]
,primaryMSDB.[notify_level_netsend]
,primaryMSDB.[notify_level_page]
,primaryMSDB.[notify_email_operator_id]
,primaryMSDB.[notify_netsend_operator_id]
,primaryMSDB.[notify_page_operator_id]
,sysoperators.name notifyEmailName
,primaryMSDB.[delete_level]
,primaryMSDB.[date_created]
,primaryMSDB.[date_modified]
,primaryMSDB.[version_number]
FROM '
+ QUOTENAME(@Machine) + '.msdb.dbo.sysJobs as primaryMSDB
LEFT JOIN ' + QUOTENAME(@Machine) + '.msdb.dbo.sysCategories as primaryCategories
ON primaryCategories.category_id = primaryMSDB.category_id
LEFT JOIN ' + QUOTENAME(@Machine) + '.msdb.dbo.sysoperators
ON sysoperators.id = primaryMSDB.notify_email_operator_id
LEFT JOIN msdb.dbo.sysJobs msdbLocal
ON primaryMSDB.name = msdbLocal.name
LEFT JOIN ' + QUOTENAME(@Machine) + '.master.sys.database_principals as primaryPrincipals
ON primaryPrincipals.sid = primaryMSDB.[owner_sid]
) InsertUpdate
WHERE Action IS NOT NULL
' AS VARCHAR(MAX))
INSERT INTO #Jobs
EXEC sp_ExecuteSQL @SQL
--select * from #Jobs
DECLARE @originalJobID UNIQUEIDENTIFIER
,@targetJobID UNIQUEIDENTIFIER
,@name [sysname]
,@enabled [tinyint]
,@description [nvarchar] (512)
,@start_step_id [int]
,@category_id [int]
,@owner_sid VARBINARY(85)
,@ownerName SYSNAME
,@notify_level_eventlog [int]
,@notify_level_email [int]
,@notify_level_netsend [int]
,@notify_level_page [int]
,@notify_email_operator_id [int]
,@notify_netsend_operator_id [int]
,@notify_page_operator_id [int]
,@email_operator_name SYSNAME
,@delete_level [int]
,@date_created [datetime]
,@date_modified [datetime]
,@version_number [int]
,@Action SYSNAME
,@CategoryName SYSNAME
DECLARE db_cursor CURSOR
FOR
SELECT job_id
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category_id]
,[categoryname]
,[owner_sid]
,[owner_name]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator_id]
,[notify_netsend_operator_id]
,[notify_page_operator_id]
,[Notify_Email_Operator_name]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,Action
FROM #Jobs
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @originalJobID
,@name
,@enabled
,@description
,@start_step_id
,@category_id
,@CategoryName
,@owner_sid
,@ownerName
,@notify_level_eventlog
,@notify_level_email
,@notify_level_netsend
,@notify_level_page
,@notify_email_operator_id
,@notify_netsend_operator_id
,@notify_page_operator_id
,@email_operator_name
,@delete_level
,@date_created
,@date_modified
,@version_number
,@Action
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Action = 'Update'
BEGIN
print 'Updating Job ' + @name
EXEC msdb.dbo.sp_update_job @job_name = @name
,@enabled = @enabled
,@description = @description
,@start_step_id = @start_step_id
,@category_name = @CategoryName
,@owner_login_Name = @ownerName
,@notify_level_eventlog = @notify_level_eventlog
,@notify_level_email = @notify_level_email
,@notify_level_netsend = @notify_level_netsend
,@notify_level_page = @notify_level_page
,@notify_email_operator_name = @email_operator_name
,@delete_level = @delete_level
SELECT @targetJobID = sysjobs.job_id
FROM msdb.dbo.sysjobs
WHERE NAME = @name
END
IF @Action = 'INSERT'
BEGIN
set @targetJobID = null
print 'Creating Job ' + @name
print 'Owner: ' + @ownerName
EXEC msdb.dbo.sp_add_job @job_name = @name
,@enabled = @enabled
,@description = @description
,@start_step_id = @start_step_id
,@category_name = @CategoryName
,@owner_login_Name = @ownerName
,@notify_level_eventlog = @notify_level_eventlog
,@notify_level_email = @notify_level_email
,@notify_level_netsend = @notify_level_netsend
,@notify_level_page = @notify_level_page
,@notify_email_operator_name = @email_operator_name
,@delete_level = @delete_level
,@job_ID = @targetJobID OUTPUT
EXEC msdb.dbo.sp_add_jobserver @job_id=@targetJobID, @server_name = @@SERVERNAME
print @targetJobID
print 'Job Created'
END
DECLARE @step_id [int]
,@step_name [sysname]
,@subsystem [nvarchar] (40)
,@command [nvarchar] (max)
,@flags [int]
,@additional_parameters [nvarchar] (max)
,@cmdexec_success_code [int]
,@on_success_action [tinyint]
,@on_success_step_id [int]
,@on_fail_action [tinyint]
,@on_fail_step_id [int]
,@server [sysname]
,@database_name [sysname]
,@database_user_name [sysname]
,@retry_attempts [int]
,@retry_interval [int]
,@os_run_priority [int]
,@output_file_name [nvarchar] (200)
,@proxy_id [int]
SET @SQL = 'SELECT step_id,
step_name,
subsystem,
command,
additional_parameters,
cmdexec_success_code,
on_success_action,
on_success_step_id,
on_fail_action,
on_fail_step_id,
server,
database_name,
database_user_name,
retry_attempts,
retry_interval,
os_run_priority,
output_file_name,
flags,
proxy_id
FROM ' + QUOTENAME(@Machine) + '.msdb.dbo.sysjobsteps
WHERE job_id = ''' + CAST(@originalJobID AS VARCHAR(100)) + ''''
DELETE
FROM #JobSteps
INSERT INTO #JobSteps
EXEC sp_ExecuteSQL @SQL
--select * From #JobSteps
IF (
SELECT COUNT(1)
from #JobSteps
LEFT JOIN msdb.dbo.sysjobsteps
ON sysjobsteps.job_id = @targetJobID
AND sysjobsteps.step_id = #JobSteps.step_id
AND sysjobsteps.step_name = #JobSteps.step_name
WHERE sysjobsteps.step_id IS NULL
) > 0
OR
(
SELECT COUNT(1)
from msdb.dbo.sysjobsteps
LEFT JOIN #JobSteps
ON sysjobsteps.step_id = #JobSteps.step_id
AND sysjobsteps.step_name = #JobSteps.step_name
WHERE sysjobsteps.step_id IS NULL
AND sysjobsteps.job_id = @targetJobID
) > 0
BEGIN
DECLARE db_jobStep_todelete_cursor CURSOR
FOR
SELECT step_id
FROM msdb.dbo.sysjobsteps
WHERE sysjobsteps.job_id = @targetJobID
order by step_id desc
OPEN db_jobStep_todelete_cursor
FETCH NEXT
FROM db_jobStep_todelete_cursor
INTO @step_id
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Deleting Step ' + cast(@step_id as varchar(5)) + ' from job ' + @name
EXEC msdb.dbo.sp_delete_jobstep @job_id = @targetJobID
,@step_id = @step_id
FETCH NEXT
FROM db_jobStep_todelete_cursor
INTO @step_id
END
CLOSE db_jobStep_todelete_cursor
DEALLOCATE db_jobStep_todelete_cursor
END
---------------------------
--cursor through all steps for the job and update steps...just do the update
DECLARE db_jobStep_cursor CURSOR
FOR
SELECT step_id
,step_name
,subsystem
,command
,flags
,additional_parameters
,cmdexec_success_code
,on_success_action
,on_success_step_id
,on_fail_action
,on_fail_step_id
,SERVER
,database_name
,database_user_name
,retry_attempts
,retry_interval
,os_run_priority
,output_file_name
,proxy_id
FROM #JobSteps
order by step_id
OPEN db_jobStep_cursor
FETCH NEXT
FROM db_jobStep_cursor
INTO @step_id
,@step_name
,@subsystem
,@command
,@flags
,@additional_parameters
,@cmdexec_success_code
,@on_success_action
,@on_success_step_id
,@on_fail_action
,@on_fail_step_id
,@server
,@database_name
,@database_user_name
,@retry_attempts
,@retry_interval
,@os_run_priority
,@output_file_name
,@proxy_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (
SELECT 1
FROM msdb.dbo.sysjobsteps
WHERE job_id = @targetJobID
AND step_id = @step_id
)
BEGIN
EXEC msdb.dbo.sp_update_jobstep @job_id = @targetJobID
,@step_id = @step_id
,@subsystem = @subsystem
,@command = @command
,@flags = @flags
,@additional_parameters = @additional_parameters
,@cmdexec_success_code = @cmdexec_success_code
,@on_success_action = @on_success_action
,@on_success_step_id = @on_success_step_id
,@on_fail_action = @on_fail_action
,@on_fail_step_id = @on_fail_step_id
,@server = @server
,@database_name = @database_name
,@database_user_name = @database_user_name
,@retry_attempts = @retry_attempts
,@retry_interval = @retry_interval
,@os_run_priority = @os_run_priority
,@output_file_name = @output_file_name
,@proxy_id = @proxy_id
END
ELSE
BEGIN
print 'creating' + @step_name
EXEC msdb.dbo.sp_add_jobstep @job_id = @targetJobID
,@step_id = @step_id
,@step_name = @step_name
,@subsystem = @subsystem
,@command = @command
,@flags = @flags
,@additional_parameters = @additional_parameters
,@cmdexec_success_code = @cmdexec_success_code
,@on_success_action = @on_success_action
,@on_success_step_id = @on_success_step_id
,@on_fail_action = @on_fail_action
,@on_fail_step_id = @on_fail_step_id
,@server = @server
,@database_name = @database_name
,@database_user_name = @database_user_name
,@retry_attempts = @retry_attempts
,@retry_interval = @retry_interval
,@os_run_priority = @os_run_priority
,@output_file_name = @output_file_name
,@proxy_id = @proxy_id
END
FETCH NEXT
FROM db_jobStep_cursor
INTO @step_id
,@step_name
,@subsystem
,@command
,@flags
,@additional_parameters
,@cmdexec_success_code
,@on_success_action
,@on_success_step_id
,@on_fail_action
,@on_fail_step_id
,@server
,@database_name
,@database_user_name
,@retry_attempts
,@retry_interval
,@os_run_priority
,@output_file_name
,@proxy_id
END
CLOSE db_jobStep_cursor
DEALLOCATE db_jobStep_cursor
----------------------------
FETCH NEXT
FROM db_cursor
INTO @originalJobID
,@name
,@enabled
,@description
,@start_step_id
,@category_id
,@CategoryName
,@owner_sid
,@ownerName
,@notify_level_eventlog
,@notify_level_email
,@notify_level_netsend
,@notify_level_page
,@notify_email_operator_id
,@notify_netsend_operator_id
,@notify_page_operator_id
,@email_operator_name
,@delete_level
,@date_created
,@date_modified
,@version_number
,@Action
END
CLOSE db_cursor
DEALLOCATE db_cursor
DROP TABLE #jobSteps
DROP TABLE #Jobs
END