Agent Jobs

Best SQL Email Alerts With SQL Database Mail Formatted With HTML And CSS

Here is some SQL logic you can use to create a nicely formatted SQL email alert using SQL Database Mail. It’s formatted properly with HTML, and CSS. It’s imbedded into email and doesn’t attach a report. It basically pulls the last known error from the error log. I created this logic so you can place it into a Job by itself, and this reference this Job (in case of failure) across any other job step across any job.

So what exactly does this do?
1. Check to see if SQL Server Database Mail has been configured. If not; it will configure it for you automatically. You do not have to have any email accounts configured already. Just supply the SMTP Server name in the logic directly and it will do the rest.
2. Sends a test email whenever the Database mail has been configured so you know where the email notification came from exactly. The Server Name, and the Instance Name are both in the email message so you’ll know pretty readily where the email came from.
3. Create temp tables based on most recent error information in SysJobs, SysJobSteps, and SysJobHistory tables.
4. Captures most recent Error information from the most recently failed Job Step including the Server Name, Job Name, Step Name, Step Error, and passes it into email.
5. Captures Job Name, and all Job Steps from the most recent failed Job including last known successful run of said steps and passes it into email.
6. Finally; 2 HTML tables, are created, and formatted with CSS to display the information.
7. Email message is sent.

The next big question… What does it look like?   Like this.  This is exactly what it will look like in email.
SQL Email Alert Built From HTML And CSS

 

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;

 ----------------------------------------------------------------------
 -- Add a profile
 execute msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'SQLDatabaseMailProfile'
 , @description = 'SQLDatabaseMail';

 ----------------------------------------------------------------------
 -- Add the account names you want to appear in the email message.
 execute msdb.dbo.sysmail_add_account_sp
 @account_name = 'EmailAddressYouWantToAppearInMessage'
 , @email_address = 'EmailAddressYouWantToAppearInMessage'
 , @mailserver_name = 'MySMTPServerName'
 --, @port = ####
 --, @enable_ssl = 1
 --, @username ='MySQLDatabaseMailProfile'
 --, @password ='MyPassword'

 -- Adding the account to the profile
 execute msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'SQLDatabaseMailProfile'
 , @account_name = 'EmailAddressYouWantToAppearInMessage'
 , @sequence_number = 1;

 -- Give access to new database mail profile (DatabaseMailUserRole)
 execute msdb.dbo.sysmail_add_principalprofile_sp
 @profile_name = 'SQLDatabaseMailProfile'
 , @principal_id = 0
 , @is_default = 1;

 ----------------------------------------------------------------------
 -- Get Server info for test message

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

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

 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLDatabaseMailProfile'
 , @recipients = 'MyEmailAddress'
 , @subject = @basic_test_subject_message
 , @body = @basic_test_body_message;

 -- Confirm message send
 -- select * from msdb..sysmail_allitems
 end

----------------------------------------------------------------------
-- get basic server info.

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

----------------------------------------------------------------------
-- set message subject.
declare @message_subject varchar(255)
set @message_subject = 'SQL Job failure found on Server: ' + @server_name_instance_name

----------------------------------------------------------------------
-- 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] int not null
, [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
order by sj.name, step_id asc

----------------------------------------------------------------------
-- create 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] int not null
, [step_name] varchar(255)
, [process_type] varchar(255)
, [last_ran] 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], [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

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

declare @xml_top NVARCHAR(MAX)
declare @xml_mid NVARCHAR(MAX)
declare @body_top NVARCHAR(MAX)
declare @body_mid NVARCHAR(MAX)

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

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

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

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

set @body_top =
 '<html>
 <head>
 <style>
 h1{
 font-family: sans-serif;
 font-size: 110%;
 }
 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>' + @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>'

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

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

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

+ @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>'

----------------------------------------------------------------------
-- send email.

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLDatabaseMailProfile'
, @recipients = 'MyEmailAddress'
, @subject = @message_subject
, @body = @body_top
, @body_format = 'HTML';

drop table #agent_job_step_error_report
drop table #agent_job_information
Advertisements

2 replies »

  1. hi this is indeed a great guide for me. but may i know if this is applicable in outlook. as most of the syntax in html doesnt work when it comes to outlook. thank you!

  2. Actually; there is a process here:
    https://mikesdatawork.wordpress.com/2016/07/01/create-job-step-notification/

    This one is per job so all you need to do is supply the Job Name, SMTP Server name, and of course your email address and it will shoot a More accurate report through email (Formatted for Outlook Email). The notification includes the job status, run time, and duration for each step.

    The image of the email is exactly how it will appear.

    This logic also includes a snipped at the bottom if you want to incorporate the server name, and job status in the Outlook email subject ( so you can see the status of the job without having to open the email ).

    Anyway; hope is this is helpful.

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