TSQL Script to automatically add a new step to TestForPrimary for SQL AlwaysOn Solution:
USE [DBA]
GO
SET NOCOUNT ON
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Job
DECLARE @jobID nvarchar(40),@stepID nvarchar(40),@stepID_nxt nvarchar(40),@procName varchar (255),
@jobName varchar (255), @command_job varchar(8000),@command_step varchar(8000),
@command_end varchar(8000),@command_sched varchar(8000),
@CommandString NVARCHAR(1000),@command_main nvarchar(max)
DECLARE @job_name as varchar(255),@owner_login_name as varchar(255),@description as varchar(255),
@category_name as varchar(255),@enabled as nvarchar(4)
DECLARE @notify_level_email as nvarchar(4),@notify_level_page as nvarchar(4),
@notify_level_netsend as nvarchar(4)
DECLARE @notify_level_eventlog as nvarchar(4),@delete_level as nvarchar(4),
@start_step_id nvarchar(4)
--Job Steps
DECLARE @step_name varchar(255),@command varchar(8000),@database_name varchar(255),
@database_user_name varchar(255), @subsystem nvarchar(40)
DECLARE @cmdexec_success_code nvarchar(2),@flags nvarchar(2),@retry_attempts nvarchar(2),
@retry_interval nvarchar(2),@output_file_name varchar (255)
DECLARE @on_success_step_id nvarchar(3),@on_success_action nvarchar(2),
@on_fail_step_id nvarchar(2),@on_fail_action nvarchar(2)
SET @CommandString = 'use master
GO
Declare @AGName varchar(10);
SELECT TOP(1) @AGName= name FROM sys.availability_groups;
if (SELECT ars.role_desc from sys.dm_hadr_availability_replica_states
ars inner join sys.availability_groups ag
on ars.group_id = ag.group_id where ag.name = @AGName and ars.is_local = 1) = ''PRIMARY''
BEGIN
PRINT ''This is Primary Replica - execute job''
END
ELSE
BEGIN
-- we''re not in the Primary - exit gracefully: Deliberately cause a Failure
SELECT 1/0
PRINT ''This is Secondary replica - exiting with success'';
END'
if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_jobs')
BEGIN
DROP TABLE DBA..server_agent_jobs
End
--ignore multi-server jobs at this time
select sj.job_id, name, '0' as executed,category_id,'1' as spcreated,
'1999-12-31 00:0000000' as createddate,
('EXECUTE [dbo].[sp_create_server_agent_job_' + name +']') as excmd,
('sp_create_server_agent_job_' + name) as spname into DBA..server_agent_jobs
from msdb..sysjobs sj inner join msdb..sysjobservers ss on ss.job_id = sj.job_id
and ss.server_id = '0' order by sj.job_id
WHILE EXISTS (select job_id from DBA..server_agent_jobs where executed = '0')
BEGIN
--get working job_id and stored proc name
SELECT top 1 @jobID = job_id, @procName = 'sp_create_server_agent_job_' + name, @jobName = name
FROM DBA..server_agent_jobs WHERE executed = '0' order by job_id
--get job info from sysjobs
select @job_name = a.name,@owner_login_name = b.name,@description = a.description,
@category_name = c.name,@enabled = a.enabled,
@notify_level_email = a.notify_level_email,@notify_level_page = a.notify_level_page,
@notify_level_netsend = a.notify_level_netsend,@notify_level_eventlog = a.notify_level_eventlog,
@delete_level = a.delete_level, @start_step_id = a.start_step_id
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c
WHERE a.owner_sid = b.sid and a.category_id = c.category_id and job_id = @jobID
--get job info from sysjobsteps
if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_steps')
BEGIN
DROP TABLE DBA..server_agent_job_steps
End
select *, '0' as executed into DBA..server_agent_job_steps
from msdb..sysjobsteps sjs where job_id = @jobID order by sjs.step_id
if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES
where table_name='server_agent_job_schedules')
BEGIN
DROP TABLE DBA..server_agent_job_schedules
End
select *, '0' as executed into DBA..server_agent_job_schedules from msdb..sysjobschedules sjs
where job_id = @jobID order by sjs.schedule_id
--drop proc if it exists before it's re-created
if EXISTS(select * from DBA.INFORMATION_SCHEMA.ROUTINES where routine_name = @procName)
BEGIN
exec('DROP PROCEDURE [' + @procName + ']')
End
SET CONCAT_NULL_YIELDS_NULL OFF
select @command_job = 'CREATE Procedure [dbo].[' + @procName + '](
@operator varchar(255)=NULL
)
AS
BEGIN
SET NOCOUNT OFF
DECLARE @ReturnCode nvarchar (40)--, @jobID nvarchar (40)
Begin Transaction
--delete job if it already exists (by job name -> @jobName)
if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''')
begin
Print ''Job Updated''
end
--add job steps
'
WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')
BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id desc
SELECT @step_name = a.step_name,@command = a.command, @database_name = a.database_name,
@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code, @flags = a.flags,
@retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID
select @command_job = @command_job +'
EXECUTE msdb.dbo.sp_delete_jobstep
@job_id = ''' + @JobID + ''',
@step_id = '+ @stepID +'
'
update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END
update DBA..server_agent_job_steps set executed = '0'
SELECT @command_step = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=''' + @JobID + ''',
@step_name =''TestForPrimary'',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=1,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,@subsystem=''TSQL'',
@command = ''' + REPLACE (@CommandString,'''','''''') + ''',
@database_name=''master'',
@flags=0
'
WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')
BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id
SELECT @step_name = a.step_name,@command = a.command,
@database_name = a.database_name,@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code,
@flags = a.flags, @retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID
SET @stepID_nxt=@stepID+1
select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = ''' + @JobID + ''',
@step_id = '''+ @stepID_nxt +''',
@step_name = ''' + @step_name + ''',
@command = ''' + REPLACE (@command,'''','''''') + ''',
@database_name = ''' + @database_name + ''',
@server = ''' + '' + ''',
@database_user_name = ''' + @database_user_name + ''',
@subsystem = ''' + @subsystem + ''',
@cmdexec_success_code = ''' + @cmdexec_success_code + ''',
@flags = ''' + @flags + ''',
@retry_attempts = ''' + @retry_attempts + ''',
@retry_interval = ''' + @retry_interval + ''',
@output_file_name = ''' + @output_file_name + ''',
@on_success_step_id = ''' + @on_success_step_id + ''',
@on_success_action = ''' + @on_success_action + ''',
@on_fail_step_id = ''' + @on_fail_step_id + ''',
@on_fail_action = ''' + @on_fail_action + '''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END
--update start step id from sysjobs
select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = ''' + @JobID + ''',
@start_step_id = ''' + @start_step_id + '''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
'
select @command_end = '
COMMIT TRANSACTION
GOTO THE_END
QuitWithRollback:
print ''job failed''
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
THE_END:
END'
--print (@command_job + @command_step + @command_sched + @command_end)
SET CONCAT_NULL_YIELDS_NULL ON
execute (@command_job + @command_step + @command_sched + @command_end)
update DBA..server_agent_jobs set executed = '1',createddate=getdate()
where DBA..server_agent_jobs.job_id = @jobID
select @command_step = ''
END
update DBA..server_agent_jobs set spcreated = 0,excmd='--NA',spname='--NA'
where spname NOT IN (SELECT SPECIFIC_Name FROM DBA.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' and SPECIFIC_NAME like 'sp_create_server_agent_job_%')
