KEMBAR78
AG Automation SQL JOB | PDF | Microsoft Sql Server | Backup
Scribd
Upload
38 views9 pages

AG Automation SQL JOB

Uploaded by

kurmirakesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
38 views9 pages

AG Automation SQL JOB

Uploaded by

kurmirakesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

USE [msdb]

GO

/****** Object: Job [AG-Refresh Orion Database] Script Date: 7/24/2019 10:09:15
PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/24/2019
10:09:15 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized
(Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)


EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AG-Refresh Orion Database',
@enabled=0,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable Trans log backup job] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable
Trans log backup job',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE msdb
GO

EXEC sp_update_job @job_name = ''DBMAINT.TLOGBK-AG01'', @enabled = 0


GO
',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Restore database; enable CDC] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore
database; enable CDC',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE master
GO
-----------------------------------------------------------------------------------
---------------------------
ALTER AVAILABILITY GROUP [AWNWKCLAG01-AG] REMOVE DATABASE [Orion]
GO
-----------------------------------------------------------------------------------
---------------------------
alter database [Orion] set single_user with rollback immediate
go
-----------------------------------------------------------------------------------
--------------------------
waitfor delay ''00:00:10''
DROP DATABASE [Orion]
GO
-----------------------------------------------------------------------------------
---------------------------
waitfor delay ''00:00:10''
RESTORE DATABASE [Orion]
FROM DISK = ''D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\Golden_Backup\Orion.bak''
WITH
MOVE ''Orion_Template'' TO ''D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\Orion.mdf'',
MOVE ''Orion_Template_log'' TO ''E:\TLOGS\Orion_log.ldf'',
KEEP_CDC,
REPLACE
GO

/*
-----------------------------------------------------------------------------------
--------------------------
USE Orion
GO
exec sys.sp_cdc_add_job ''capture''
GO
exec sys.sp_cdc_add_job ''cleanup''
GO
UPDATE [msdb].[dbo].[cdc_jobs]
SET [retention] = 525600
WHERE job_type =''cleanup'' AND database_id = (SELECT database_id FROM
sys.databases WHERE name = ''Orion'')
GO
*/
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Add Security Groups Rights] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Add
Security Groups Rights',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE Orion
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Phoenix Development'')
Begin
CREATE USER [CBS\Phoenix Development] FOR LOGIN [CBS\Phoenix Development]
WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Phoenix Development]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Phoenix Development]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\svc.downstream'')
Begin
CREATE USER [CBS\svc.downstream] FOR LOGIN [CBS\svc.downstream] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\svc.downstream]
ALTER ROLE [db_owner] ADD MEMBER [CBS\svc.downstream]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Functional
Support'')
Begin
CREATE USER [CBS\Network Sales Functional Support] FOR LOGIN [CBS\Network
Sales Functional Support] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Functional Support]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Network Sales Functional Support]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\#COE_IBMS_ADM'')
Begin
CREATE USER [CBS\#COE_IBMS_ADM] FOR LOGIN [CBS\#COE_IBMS_ADM] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\#COE_IBMS_ADM]
ALTER ROLE [db_owner] ADD MEMBER [CBS\#COE_IBMS_ADM]
End
GO

IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Business'')
Begin
CREATE USER [CBS\Network Sales Business] FOR LOGIN [CBS\Network Sales
Business] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Business]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Network Sales Business]
End
GO

IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''cbs\Servman1'')


Begin
CREATE USER [cbs\Servman1] FOR LOGIN [cbs\Servman1] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [cbs\Servman1]
ALTER ROLE [db_owner] ADD MEMBER [cbs\Servman1]
End
GO
USE Pentagon
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Phoenix Development'')
Begin
CREATE USER [CBS\Phoenix Development] FOR LOGIN [CBS\Phoenix Development]
WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Phoenix Development]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Phoenix Development]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\svc.downstream'')
Begin
CREATE USER [CBS\svc.downstream] FOR LOGIN [CBS\svc.downstream] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\svc.downstream]
ALTER ROLE [db_owner] ADD MEMBER [CBS\svc.downstream]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Functional
Support'')
Begin
CREATE USER [CBS\Network Sales Functional Support] FOR LOGIN [CBS\Network
Sales Functional Support] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Functional Support]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Network Sales Functional Support]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\#COE_IBMS_ADM'')
Begin
CREATE USER [CBS\#COE_IBMS_ADM] FOR LOGIN [CBS\#COE_IBMS_ADM] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\#COE_IBMS_ADM]
ALTER ROLE [db_owner] ADD MEMBER [CBS\#COE_IBMS_ADM]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Business'')
Begin
CREATE USER [CBS\Network Sales Business] FOR LOGIN [CBS\Network Sales
Business] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Business]
ALTER ROLE [db_owner] ADD MEMBER [CBS\Network Sales Business]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''cbs\Servman1'')
Begin
CREATE USER [cbs\Servman1] FOR LOGIN [cbs\Servman1] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [cbs\Servman1]
ALTER ROLE [db_owner] ADD MEMBER [cbs\Servman1]
End
GO

USE CAMS
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Phoenix Development'')
Begin
CREATE USER [CBS\Phoenix Development] FOR LOGIN [CBS\Phoenix Development]
WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Phoenix Development]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Phoenix Development]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\svc.downstream'')
Begin
CREATE USER [CBS\svc.downstream] FOR LOGIN [CBS\svc.downstream] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\svc.downstream]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\svc.downstream]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Functional
Support'')
Begin
CREATE USER [CBS\Network Sales Functional Support] FOR LOGIN [CBS\Network
Sales Functional Support] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Functional Support]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Network Sales Functional Support]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\#COE_IBMS_ADM'')
Begin
CREATE USER [CBS\#COE_IBMS_ADM] FOR LOGIN [CBS\#COE_IBMS_ADM] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\#COE_IBMS_ADM]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\#COE_IBMS_ADM]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Business'')
Begin
CREATE USER [CBS\Network Sales Business] FOR LOGIN [CBS\Network Sales
Business] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Business]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Network Sales Business]
End
GO

USE CAMS_Cable
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Phoenix Development'')
Begin
CREATE USER [CBS\Phoenix Development] FOR LOGIN [CBS\Phoenix Development]
WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Phoenix Development]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Phoenix Development]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\svc.downstream'')
Begin
CREATE USER [CBS\svc.downstream] FOR LOGIN [CBS\svc.downstream] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\svc.downstream]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\svc.downstream]
End
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Functional
Support'')
Begin
CREATE USER [CBS\Network Sales Functional Support] FOR LOGIN [CBS\Network
Sales Functional Support] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Functional Support]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Network Sales Functional Support]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\#COE_IBMS_ADM'')
Begin
CREATE USER [CBS\#COE_IBMS_ADM] FOR LOGIN [CBS\#COE_IBMS_ADM] WITH
DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\#COE_IBMS_ADM]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\#COE_IBMS_ADM]
End
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = ''CBS\Network Sales Business'')
Begin
CREATE USER [CBS\Network Sales Business] FOR LOGIN [CBS\Network Sales
Business] WITH DEFAULT_SCHEMA=[dbo]
GRANT CONNECT TO [CBS\Network Sales Business]
ALTER ROLE [db_datareader] ADD MEMBER [CBS\Network Sales Business]
End
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup database on Primary; Add to AG group] Script Date:
7/24/2019 10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup
database on Primary; Add to AG group',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP DATABASE [Orion]
TO DISK = N''D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\Orion_Secondary.bak''
WITH INIT, COMPRESSION
GO

waitfor delay ''00:00:05''


ALTER AVAILABILITY GROUP [AWNWKCLAG01-AG]
ADD DATABASE [Orion];
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Restore database on AWNWKDSNST02] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore
database on AWNWKDSNST02',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -SAWNWKDSNST02 -E -Q "DROP DATABASE [Orion]; RESTORE
DATABASE [Orion] FROM DISK = N''\\AWNWKDSNST01\Backup\Orion_Secondary.bak'' WITH
MOVE ''Orion_Template'' TO ''D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\DATA\Orion.mdf'',
MOVE ''Orion_Template_log'' TO ''E:\TLOGS\Orion_log.ldf'', NORECOVERY"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup log on Primary] Script Date: 7/24/2019 10:09:16 PM
******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup log
on Primary',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP LOG [Orion] TO DISK = N''D:\DATA\
MSSQL11.MSSQLSERVER\MSSQL\Backup\Tranlogs\Orion_Secondary.trn''
WITH INIT, COMPRESSION
GO
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Restore Log on AWNWKDSNST02] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore Log
on AWNWKDSNST02',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -SAWNWKDSNST02 -E -Q "RESTORE LOG [Orion] FROM DISK
= N''\\AWNWKDSNST01\Backup\Tranlogs\Orion_Secondary.trn'' WITH NORECOVERY"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Truncate Reports Table In Orion] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate
Reports Table In Orion',
@step_id=8,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'TRUNCATE TABLE report_subscription_email_destination',
@database_name=N'Orion',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Start Sync on AWNWKDSNST02] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Start Sync
on AWNWKDSNST02',
@step_id=9,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -SAWNWKDSNST02 -E -Q "ALTER DATABASE [Orion] SET HADR
AVAILABILITY GROUP=[AWNWKCLAG01-AG]"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [clear SQLPERF stats buffer] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'clear
SQLPERF stats buffer',
@step_id=10,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [master]
GO
DBCC SQLPERF (''sys.dm_os_wait_stats'', CLEAR);
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Flush buffers on AWNWKDSNST02] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Flush
buffers on AWNWKDSNST02',
@step_id=11,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -SAWNWKDSNST02 -E -Q "DBCC SQLPERF
(''sys.dm_os_wait_stats'', CLEAR);"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Reenable Trans log backup job] Script Date: 7/24/2019
10:09:16 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reenable
Trans log backup job',
@step_id=12,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE msdb
GO

EXEC sp_update_job @job_name = ''DBMAINT.TLOGBK-AG01'', @enabled = 1


GO',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete temp backup files] Script Date: 7/24/2019 10:09:16
PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete temp
backup files',
@step_id=13,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'DEL D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\Orion-
Secondary.*
DEL D:\DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\Pentagon-Secondary.*
',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =
N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

You might also like