Knowing if your jobs have succeeded or failed can be quite handy. Especially when it comes to knowing whether your backups are failing or if you have ETLs that are failing. You can choose to set up email notifications on each job, although if you have multiple servers you could be receiving a lot of email throughout the day.
There is a simple script where you can email out the job history in a HTML table format for all the jobs executed on a server in one email. You can then set this up on each server and have one mail per server for you to check every day. This script can be scheduled as a job that executes a T-SQL query.
First things first, you need to make sure you have database mail set up on the server and have a default account set. You can use your company’s SMTP server settings, or if you are doing this for yourself, you can use Gmail. There are many more to use besides Gmail.
For this post I shall be setting up the database mail settings with Gmail’s SMTP server details.
Part 1: Setting up database mail
Expand the Management node in Object Explorer. Right-click on Database Mail and select Configure Database Mail.
Click next on the Wizard start screen and by default the Set up Database Mail radio button will be selected. You can click next on this window to go through the steps of setting up Database Mail.
Next you will set up a Profile name for your database mail. I have chosen Job Execution mail to make it relevant for what it will be used for.
An SMTP account will need to be added. You will select Add on the same New Profile window.
Next complete the mail account details. I have set mine up to use my Gmail account. If you are using your company’s SMTP mail server details, you will need to find out the details and the port number to use. In my experience I have used Anonymous authentication when using a company SMTP server. Once everything is filled in you will click on OK.
You shall see your newly created profile for your mails. Then select next.
In the next window you need to manage your profile security. You shall see the profile name you just created. You will need to set this at the default profile for the sp_send_dbmail to use when sending out the emails. Then click on next.
In the next window you can configure system parameters. I choose to leave the settings that has been recommended by Microsoft SQL Server and click next.
A completion wizard will appear with a summary of everything that will be created and you can click on Finish.
Once all has executed you shall see green ticks next to each process that was completed. Finally you can click close and the database mail is now set up.
Now that the database mail is set up you can you can use the script to email out your job execution history in HTML format.
Part 2: HTML email script
The first section of the script is where you set up your HTML table and place your query for what you want to email out. You can also style the table to have background colours and set padding (you might need to brush up on some CSS).
At the end of the table set up you will set up who you want this to be emailed to, the subject, body and body format.
To test you can run this from within a new query window in SSMS. Later on if you choose you can set this up as a SQL job to email out once day or as frequently as you prefer.
You can download the script for the Email Jobs Query here.