Created
February 7, 2020 11:09
-
-
Save Alex-Yates/1da6edc9cd07f204764d7c532bab8ebf to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- First we delete the job if it allready exists | |
-- Note: use @job_name instead of @job_id for the delete (SQL default is to use ID, but that only works first time) | |
-- Note: use @delete_history=0 to maintain job history. History will still be deleted in SSMS Agent GUI. | |
-- However, it will be persisted in sysjobhistory table in the msdb database. | |
/****** Object: Job [TestyMcJobFace] Script Date: 07/02/2020 10:39:22 ******/ | |
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestyMcJobFace') | |
EXEC msdb.dbo.sp_delete_job @job_name=N'TestyMcJobFace', @delete_unused_schedule=1, @delete_history=0 | |
GO | |
-- Then we redeploy the job as follows: | |
/****** Object: Job [TestyMcJobFace] Script Date: 07/02/2020 10:39:22 ******/ | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 07/02/2020 10:39:22 ******/ | |
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) | |
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'TestyMcJobFace') | |
if (@jobId is NULL) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestyMcJobFace', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@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'DESKTOP-PO8SQUD\ajyma', @job_id = @jobId OUTPUT | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
/****** Object: Step [do a testface] Script Date: 07/02/2020 10:39:22 ******/ | |
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1) | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'do a testface', | |
@step_id=1, | |
@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'TSQL', | |
@command=N'select ''testface''', | |
@database_name=N'master', | |
@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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment