Agent Jobs

Create The Job SEND SQL JOB ALERTS (beta)

Here’s the sql logic to create the Job SEND SQL JOB ALERTS (beta).  As usual in case the code gets butchered by the blog post; I’ll go ahead and include the pdf that shows you all the logic.
CREATE THE SEND SQL JOB ALERTS (beta) Job

USE [msdb]
GO

/****** Object: Job [SEND SQL JOB ALERTS (beta)] Script Date: 7/15/2015 4:19:20 PM ******/ BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 7/15/2015 4:19:20 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’SEND SQL JOB ALERTS (beta)’, @enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’This is a new SQL Job notification. It”s designed to send the a short error report about step failure within a job.’, @category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’MyDomain\MyEmailAdmin’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send email about step failure] Script Date: 7/15/2015 4:19:21 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Send email about step failure’, @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’use msdb;
set nocount on

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s