Agent Jobs

Create Complete SQL Job Notification System With HTML CSS Alerting

The following logic will configure your SMTP Email notification, Create a Job to send notifications out in an HTML & CSS format, and create a Trigger on the MSDB SysJobHistoryDatabase. Each time a ‘failure’ entry is inserted into the table, the trigger kicks off the Job.

In case this logic gets butchered by the blog auto-whatevers I’m attaching a PDF that has all the logic in it.

</pre>
sql logic - CREATE COMPLETE JOB NOTIFICATION

&nbsp;

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

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

&nbsp;

END

&nbsp;

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'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Send email about step failure]    Script Date: 7/15/2015 12:24:12 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

----------------------------------------------------------------------

-- Configure SQL Database Mail if it''s not already configured.

if (select top 1 name from msdb..sysmail_profile) is null

begin

----------------------------------------------------------------------

-- Enable SQL Database Mail

exec master..sp_configure ''show advanced options'',1

reconfigure;

exec master..sp_configure ''database mail xps'',1

reconfigure;

&nbsp;

----------------------------------------------------------------------

-- Add a profile

execute msdb.dbo.sysmail_add_profile_sp

@profile_name                                = ''SQLDatabaseMailProfile''

,               @description                     = ''SQLDatabaseMail'';

&nbsp;

----------------------------------------------------------------------

-- Add the account names you want to appear in the email message.

execute msdb.dbo.sysmail_add_account_sp

@account_name                             = ''sqldatabasemail@mydomain.com''

,               @email_address                              = ''sqldatabasemail@mydomain.com''

,               @mailserver_name        = ‘‘MySMTPServerName.MyDomain.com’’

--,           @port                                   = ####  --optional

--,           @enable_ssl                      = 1 --optional

--,           @username                       =''MySQLDatabaseMailProfile'' --optional

--,           @password                        =''MyPassword'' --optional

&nbsp;

-- Adding the account to the profile

execute msdb.dbo.sysmail_add_profileaccount_sp

@profile_name                                = ''SQLDatabaseMailProfile''

,               @account_name                             = ''sqldatabasemail@mydomain.com''

,               @sequence_number     = 1;

&nbsp;

-- Give access to new database mail profile (DatabaseMailUserRole)

execute msdb.dbo.sysmail_add_principalprofile_sp

@profile_name                                = ''SQLDatabaseMailProfile''

,               @principal_id                    = 0

,               @is_default                       = 1;

&nbsp;

----------------------------------------------------------------------

-- Get Server info for test message

&nbsp;

declare @get_basic_server_name                                                          varchar(255)

declare @get_basic_server_name_and_instance_name                              varchar(255)

declare @basic_test_subject_message                                 varchar(255)

declare @basic_test_body_message                                                     varchar(max)

set                          @get_basic_server_name                                          = (select cast(serverproperty(''servername'') as varchar(255)))

set                          @get_basic_server_name_and_instance_name              = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

set                          @basic_test_subject_message                 = ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name

set                          @basic_test_body_message                                     = ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

&nbsp;

----------------------------------------------------------------------

-- Send quick email to confirm email is properly working.

&nbsp;

EXEC msdb.dbo.sp_send_dbmail

@profile_name                                = ''SQLDatabaseMailProfile''

,               @recipients                        = “MyEmailAddressGoesHere

,               @subject                                             = @basic_test_subject_message

,               @body                                 = @basic_test_body_message;

&nbsp;

-- Confirm message send

-- select * from msdb..sysmail_allitems

end

&nbsp;

----------------------------------------------------------------------

-- get basic server info.

&nbsp;

declare @server_name_basic                    varchar(255)

declare @server_name_instance_name              varchar(255)

declare @server_time_zone                                      varchar(255)

set          @server_name_basic                    = (select cast(serverproperty(''servername'') as varchar(255)))

set          @server_name_instance_name              = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

exec      master.dbo.xp_regread ''hkey_local_machine'', ''system\currentcontrolset\control\timezoneinformation'',''timezonekeyname'', @server_time_zone out

&nbsp;

----------------------------------------------------------------------

-- set message subject.

declare @message_subject                        varchar(255)

set          @message_subject                        = ''SQL Job failure found on Server:  '' + @server_name_instance_name

&nbsp;

----------------------------------------------------------------------

-- find most recent error step error in sysjobhistory and pull name based on instance_id from history table.

&nbsp;

declare                 @last_error                        varchar(255)

declare @last_error_job_name                varchar(255)

set          @last_error                        = ( select top 1 instance_id from sysjobhistory where message like ''%The step failed%'' order by run_date desc )

set          @last_error_job_name                = ( select sj.name from sysjobs sj join sysjobhistory sjh on sj.job_id = sjh.job_id where instance_id = @last_error )

&nbsp;

----------------------------------------------------------------------

-- create temp table to store error information.

&nbsp;

if object_id(''tempdb..#agent_job_step_error_report'') is not null

drop table #agent_job_step_error_report

&nbsp;

create table #agent_job_step_error_report

(

[id]                                         int identity (1,1)

,               [server_name]                 varchar(255)

,               [time_of_error]                               varchar(255)

,               [job_name]                        varchar(255)

,               [step_id]                                             int not null

,               [step_name]                     varchar(255)

,               [duration]                                           varchar(255)

,               [error_message]                              varchar(max)

)

&nbsp;

----------------------------------------------------------------------

-- get information from job system tables for job step error report.

&nbsp;

insert into #agent_job_step_error_report ([server_name], [time_of_error], [job_name], [step_id], [step_name], [duration], [error_message])

select

''server name''                  = @@servername

,               ''time of error''                  = datename(dw, msdb.dbo.agent_datetime(run_date, run_time) ) + '':  '' + convert(char, msdb.dbo.agent_datetime(run_date, run_time) , 9)

,               ''job name''                                         = sj.name

,               ''step id''                                              = sjh.step_id

,               ''step name''                      = sjh.step_name

,               ''duration''                                           = CAST(sjh.run_duration/10000 as varchar)  + '':'' + CAST(sjh.run_duration/100%100 as varchar) + '':'' + CAST(sjh.run_duration%100 as varchar)

,               ''error message''                               = sjh.message

from

msdb..sysjobs sj join msdb..sysjobhistory sjh on sj.job_id = sjh.job_id

where

instance_id = @last_error

order by sj.name, step_id asc

&nbsp;

----------------------------------------------------------------------

-- create temp table to store job information

&nbsp;

if object_id(''tempdb..#agent_job_information'') is not null

drop table #agent_job_information

&nbsp;

create table #agent_job_information

(

[id]                                                         int identity (1,1)

,               [job_name]                                        varchar(255)

,               [step_id]                                                             int not null

,               [step_name]                                     varchar(255)

,               [process_type]                                 varchar(255)

,               [last_ran]                                                            varchar(255)

)

&nbsp;

----------------------------------------------------------------------

-- get all job, and step info for quick reference including the previous run duration and the last known run timestamp before the most previous error.

&nbsp;

insert into #agent_job_information ([job_name], [step_id], [step_name], [process_type], [last_ran])

select

''job name''                                                         = sj.name

,               ''step id''                                                              = sjs.step_id

,               ''step name''                                      = sjs.step_name

,               ''process type''                                  = sjs.subsystem

,               ''last ran''                                                             = datename(dw, dateadd(millisecond, sjs.last_run_time,convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10))))) + '':  '' + convert(char, dateadd(millisecond, sjs.last_run_time,convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10)))), 9)

from

msdb..sysjobs sj join msdb..sysjobsteps sjs on sj.job_id = sjs.job_id

where

sj.name = @last_error_job_name

order by

sj.name, sjs.step_id asc

&nbsp;

----------------------------------------------------------------------

-- create conditions for html tables in top and mid sections of email.

&nbsp;

declare @xml_top                                           NVARCHAR(MAX)

declare @xml_mid                                          NVARCHAR(MAX)

declare @body_top                                        NVARCHAR(MAX)

declare @body_mid                                       NVARCHAR(MAX)

&nbsp;

----------------------------------------------------------------------

-- set xml top table td''s

-- create html table object for: #agent_job_step_error_report

set @xml_top =

cast(

(select

[server_name] as ''td''

,               ''''

,               [time_of_error]               as ''td''

,               ''''

,               [job_name]        as ''td''

,               ''''

,               [step_id]                             as ''td''

,               ''''

,               [step_name]     as ''td''

,               ''''

,               [duration]                           as ''td''

,               ''''

,               [error_message]              as ''td''

from  #agent_job_step_error_report

--order by rank

for xml path(''tr'')

,               elements)

as NVARCHAR(MAX)

)

&nbsp;

----------------------------------------------------------------------

-- set xml mid table td''s

-- create html table object for: #agent_job_information

set @xml_mid =

cast(

(select

[job_name]                        as ''td''

,               ''''

,               [step_id]                                             as ''td''

,               ''''

,               [step_name]                     as ''td''

,               ''''

,               [process_type]                 as ''td''

,               ''''

,               [last_ran]                                            as ''td''

&nbsp;

from  #agent_job_information

order by [job_name], [step_id] asc

for xml path(''tr'')

,               elements)

as NVARCHAR(MAX)

)

&nbsp;

----------------------------------------------------------------------

&nbsp;

set @body_top =

''<html>

<head>

<style>

h1{

font-family: sans-serif;

font-size: 110%;

}

h3{

font-family: sans-serif;

color: red;

}

&nbsp;

table, td, tr, th {

font-family: sans-serif;

border: 1px solid black;

border-collapse: collapse;

}

th {

text-align: left;

background-color: gray;

color: white;

padding: 5px;

}

&nbsp;

td {

padding: 5px;

}

</style>

</head>

<body>

<H3>'' + @message_subject + ''</H3>

<h1>Note: The Server Time is operating on: '' + @server_time_zone + ''

<table border = 1>

<tr>

<th> Server Name           </th>

<th> Time of Error           </th>

<th> Job Name </th>

<th> Step ID       </th>

<th> Step Name               </th>

<th> Duration    </th>

<th> Error Message        </th>

</tr>''

&nbsp;

set @body_top = @body_top + @xml_top + ''</table>

&nbsp;

<h1>Quick Reference: Job Info</h1>

&nbsp;

<table border = 1>

<tr>

<th> Job Name </th>

<th> Step ID       </th>

<th> Step Name               </th>

<th> Process Type           </th>

<th> Last Ran     </th>

</tr>''

&nbsp;

+ @xml_mid + ''</table>

<h1>Go to the server by pasting in the following text under: Start-Run, or (Win + R)</h1>

<h1>mstsc -v:'' + @server_name_basic + ''</h1>''

+ ''</body></html>''

&nbsp;

----------------------------------------------------------------------

-- send email.

&nbsp;

EXEC msdb.dbo.sp_send_dbmail

@profile_name                                = ''SQLDatabaseMailProfile''

,               @recipients                        = “MyEmailAddressGoesHere

,               @subject                                             = @message_subject

,               @body                                 = @body_top

,               @body_format                 = ''HTML'';

&nbsp;

drop table #agent_job_step_error_report

drop table #agent_job_information

&nbsp;

&nbsp;

',

@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:

&nbsp;

GO

&nbsp;

&nbsp;

&nbsp;

use msdb;

set nocount on

set ansi_nulls on

set quoted_identifier on

go

create trigger [dbo].[check_for_job_failure] on  msdb..sysjobhistory

after insert

as

begin

set nocount on

declare @is_fail                                int

set                          @is_fail                                = (select case when [message] like '%The step failed%' then 1 else 0 end from msdb..sysjobhistory where instance_id in (select max(instance_id) from msdb..sysjobhistory))

if                             @is_fail                                = 1

begin

exec msdb.dbo.sp_start_job @job_name = 'SEND SQL JOB ALERTS (beta)'

end

end

&nbsp;
<pre>
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