1

I often have to alter jobs i.e. adding steps, changing the stored procedures the job calls, etc. and I always need to do the same thing on all servers in my availability group.

when dealing with jobs, the job_id in server A is not the same job_id in server B for instance, so I always have to find the job_id manually.

how could I automate this?

below is an example where I add an extra step to an existing job. I need to have a look at all the servers in the availability group and go server by server finding the job_id and doing the changes.

-- do a job alteration on all the servers in the availability group
-- get all servers in the availability group

-- need to be on the primary server in order to run the following:
select ar.replica_server_name,
ars.role_desc,
ar.failover_mode_desc,
ars.synchronization_health_desc,
ars.operational_state_desc,
CASE ars.connected_state
WHEN 0 THEN 'Disconnected'
WHEN 1 THEN 'Connected'
ELSE ''
END as ConnectionState

from sys.dm_hadr_availability_replica_states ars

inner join sys.availability_replicas ar 
        on ars.replica_id = ar.replica_id
       and ars.group_id = ar.group_id


--=========================================================
-- ON THE_SERVER_01
--=========================================================

USE [msdb]
GO

sp_help_job @job_name=N'JUNO - Update Time Expired Applicants'
--JUNO - Update Time Expired Applicants

--3868EB02-BC27-4F2C-9593-7526705FB1DA
GO

/****** Object:  Step [Update Applicants]    Script Date: 02/08/2017 13:56:28 ******/
EXEC msdb.dbo.sp_delete_jobstep 
@job_id=N'3868EB02-BC27-4F2C-9593-7526705FB1DA', @step_id=1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep 
@job_id=N'3868EB02-BC27-4F2C-9593-7526705FB1DA', @step_name=N'Update Applicants', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=4, 
        @on_success_step_id=2, 
        @on_fail_action=4, 
        @on_fail_step_id=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC usp_upd_applicationStatusTimeExpired', 
        @database_name=N'JUNOCore', 
        @output_file_name=N'E:\Jobs Output\JUNO_-_Update_Time_Expired_Applicants_$(ESCAPE_SQUOTE(STEPID))_Update_Applicants.txt', 
        @flags=20
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep 
@job_id=N'3868EB02-BC27-4F2C-9593-7526705FB1DA', 
@step_name=N'run the peocedure [app].[usp_upd_applicationStatusTimeExpired]', 
        @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 [APCore].[app].[usp_upd_applicationStatusTimeExpired]
', 
        @database_name=N'master', 
        @flags=20
GO

--=========================================================
-- ON THE_SERVER_01
--=========================================================


USE [msdb]
GO

sp_help_job @job_id=N'216381ee-a2da-4fa5-9ca8-aad98dd2ad50'
--JUNO - Update Time Expired Applicants


GO
EXEC msdb.dbo.sp_delete_jobstep 
@job_id=N'216381ee-a2da-4fa5-9ca8-aad98dd2ad50', 
@step_id=1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_id=N'216381ee-a2da-4fa5-9ca8-aad98dd2ad50', 
@step_name=N'Update Applicants', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=4, 
        @on_success_step_id=2, 
        @on_fail_action=4, 
        @on_fail_step_id=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC usp_upd_applicationStatusTimeExpired', 
        @database_name=N'JUNOCore', 
        @output_file_name=N'E:\Jobs Output\JUNO_-_Update_Time_Expired_Applicants_$(ESCAPE_SQUOTE(STEPID))_Update_Applicants.txt', 
        @flags=20
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep 
@job_id=N'216381ee-a2da-4fa5-9ca8-aad98dd2ad50', 
@step_name=N'run the peocedure [app].[usp_upd_applicationStatusTimeExpired]', 
        @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 [APCore].[app].[usp_upd_applicationStatusTimeExpired]
', 
        @database_name=N'master', 
        @flags=20
GO
  • 2
    One option dbatools.io/functions/copy-sqljob – SqlWorldWide Aug 2 '17 at 14:44
  • YES! Love dbatools!! – SQL_Hacker Aug 2 '17 at 15:37
  • @SQL_Hacker and why you did not mention those tools in your answer then? – Marcello Miorelli Aug 2 '17 at 15:46
  • I forgot about them, and at my present job, I cannot use them, so I rely on "basic" t-sql to get stuff done. :) – SQL_Hacker Aug 2 '17 at 16:44
  • Are you not allowed to use powershell at all? Pure TSQL would make this unnecessarily challenging. – Jonathan Fite Aug 2 '17 at 17:15
2

Finding the job_id is not necessary if the job is named the same on all replicas. You can use the @job_name parameter, and never have to know the job_id. See this link for an example: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobstep-transact-sql. A more specific answer to your question is to use @job_id = N'<job_name here>.

| improve this answer | |
  • that's true indeed, I just use job_id when I use scripts generated by sql server – Marcello Miorelli Aug 2 '17 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.