Agent Jobs

How To Create HTML And CSS Email Alert Notification With Sp_Send_Dbmail

How To Create HTML And CSS Email Alert Notification With Sp_Send_Dbmail

Here’s a process I created to email a fairly nice HTML & CSS formatted email (not using attachments) which displays Job Error, and includes the Step name, and error message. It will look like this:

What This will do is create a couple temporary tables to hold the Agent Job History information, then pass that information into the sp_send_dbmail using CSS and HTML formatting.

Special thanks to Bru Medishetty at MSSQLTIPS for his original write up for “Send email in a tabular format using SQL Server database mail” which used for some reference syntax. His original write-up can be found here: http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

use msdb;
set nocount on

-- find most recent error step error in sysjobhistory and pull name based on instance_id from history table.
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 )

-- create temp table to store error information.
if object_id('tempdb..#agent_job_step_error_report') is not null
       drop table #agent_job_step_error_report

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]                  varchar(255)
,      [step_name]                varchar(255)
,      [duration]                 varchar(255)
,      [error_message]            varchar(max)
)

-- get information from job system tables for job step error report.
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


-- crate temp table to store job information
if object_id('tempdb..#agent_job_information') is not null
       drop table #agent_job_information

create table #agent_job_information
(
       [id]                 int identity (1,1)
,      [job_name]           varchar(255)
,      [step_id]            varchar(255)
,      [step_name]          varchar(255)
,      [process_type]       varchar(255)
,      [previous_run]       varchar(255)
)

-- 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.
insert into #agent_job_information ([job_name], [step_id], [step_name], [process_type], [previous_run])
select
       'job name'                        = sj.name
,      'step id'                         = sjs.step_id
,      'step name'                       = sjs.step_name
,      'process type'                    = sjs.subsystem
,      'previous run'                    = 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

-- create html tables top
declare @xml_top NVARCHAR(MAX)
declare @body_top NVARCHAR(MAX)


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)
              )

set @body_top =
              '<html>
              <head>
                     <style>
                                  h3{
                                         font-family: sans-serif;
                                         color: red;
                                  }

                                  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;
                                  }

                                  td {
                                         padding: 5px;
                                  }

                                  
                     </style>
              </head>
              <body>
              <H3>SQL Agent Job Error</H3>
              <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>'

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


/*
-- create html tables mid
declare @xml_mid NVARCHAR(MAX)
declare @body_mid NVARCHAR(MAX)


set @xml_mid = 
       cast(
              (select 
                     [job_name]           as 'td'
              ,      ''
              ,      [step_id]            as 'td'
              ,      ''
              ,      [step_name]          as 'td'
              ,      ''
              ,      [process_type]       as 'td'
              ,      ''
              ,      [previous_run]       as 'td'

              from  #agent_job_information 
              order by [job_name], [step_id] asc 
              for xml path('tr')
       ,      elements)
       as NVARCHAR(MAX)
              )

set @body_mid =
              '<html>
              <head>
                     <style>
                                  H3{
                                         font-family: sans-serif;
                                         color: red;
                                  }

                                  table, td, tr, th {
                                         font-family: sans-serif;
                                         border: 1px solid black;
                                         border-collapse: collapse;
                                  }
                                  
                     </style>
              </head>
              <body>
              <H3>About Job</H3>
              <table border = 1>
              <tr>
                     <th> Job Name        </th>
                     <th> Step ID         </th>
                     <th> Step Name             </th>
                     <th> Process Type    </th>
                     <th> Previous Run    </th>
              </tr>'

set @body_mid = @body_mid + @xml_mid + '</table></body></html>'
*/
--declare     @job_error_report    varchar(255)
--set         @job_error_report = @body_top + + @body_mid

EXEC msdb.dbo.sp_send_dbmail
       @profile_name        = 'SQLDatabaseMailProfile'
,      @recipients                = 'MyEmailAddress or Distribution Group’   Distribution groups always preferred here.
,      @subject                   = 'Database Mail Subject... E-mail in Tabular Format'
,      @body                      = @body_top
,      @body_format         = 'HTML';

drop table #agent_job_step_error_report
drop table #agent_job_information
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