SQL AlwaysON : TSQL Script to automatically add a new step to TestForPrimary

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_%')