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

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s