Today’s #TipTuesday is a bit different from my normal type of post. In the course of monitoring some integrations, I had wanted to change the emails I get when there were issues. What I inherited was email alerts with attachments, without any data that I could use as an Outlook filter to tell me if something was ok or not. I had to open the attachment to see if there were errors. I wanted to find something better.
What I’m writing about today is what I’ve pieced together from various searches and unfortunately, while I got the pieces from various other blogs, I’ve changed so many things and re-worked this so many times, I no longer even have the references of those that helped me get to the point of what I’m sharing today. I wish I could link to them to thank them.
Overview of my process
The basis for this is that I want to be alerted to something on a regular basis, and if my criteria is met, send me an email with the info I need to respond or act on it. In one case, I want to get an email if there are any errors in my nightly integrations, as an example. The key for me was, I want the errors in the body of the email, I don’t want to have to open an attachment nor have to log into the server to read the errors. “There was an error” isn’t good enough for my liking!
So… these are the pieces of my process with examples of my SQL code. It’s fairly rudimentary but you can beef it up however you wish to make it fancier if you wish! All of the pieces together I have in a SQL Agent job as a step. I could probably create a stored procedure for them which would be cleaner and execute that from the job instead but I haven’t ventured that far yet.
Part 1 – Set up variables for email details
This is optional but it might make the rest easier to have the pertinent details right at the top of the code instead of having to search through the code to find what email address something is going to.
-- declare and set variables for the email DECLARE @subject varchar(50) DECLARE @recipient varchar(50) SET @subject = 'My SQL Alert' SET @recipient = 'firstname.lastname@example.org'
Part 2 – Create a temp table for the details
In the situation I was creating this for, I was checking a few different things in SQL and any one of them might trigger an alert. Instead of having multiple jobs, I chose to create a temp table and one job that “inserts” data into it if there are issues that fit my criteria. This temp table holds the “list” of things that will become the body of the email.
This is a simple 3 column table and the contents will be put into a 3 column HTML table in the body of the email.
-- Create temp table for tracking details of whatever I want to see in the body of my email alert IF OBJECT_ID('tempdb..##AlertContent') IS NOT NULL DROP TABLE ##AlertContent CREATE TABLE ##AlertContent (AlertType varchar(20), AlertTitle varchar(40), AlertDesc varchar(200) )
Part 3 – Insert some criteria
In this example – only to demonstrate something simple – I’m pretending I want to know if a new GP company was created and I want to see a user list to monitor new logins. I’ve got 2 insert statements that add things to my temp table.
NOTE: this ultimately is code I’m pasting as a step in a SQL Agent job, and I needed to fully qualify my table names (db.schema.table) since the actual send_db_mail procedure I’m calling is in msdb.
-- Example: find all companies created this year BEGIN INSERT INTO ##AlertContent SELECT 'New Database' as AlertType, ('Database: ' + INTERID) as AlertTitle, ('Company Name: ' + CMPNYNAM) as AlertDesc FROM DYNAMICS..SY01500 WHERE YEAR(CREATDDT) = 2019 END -- Continue as needed if you want to have multiple alerts in the same email -- This example is the user list with some fake criteria BEGIN INSERT INTO ##AlertContent SELECT 'New Users' as AlertType, ('User: ' + USERID) as AlertTitle, ('Name: ' + USERNAME) as AlertDesc FROM DYNAMICS..SY01400 WHERE LEFT(USERID,10) <> 'LESSONUSER' END
Part 4 – Format an HTML table & send mail
The next step is code I mostly found elsewhere and modified over time to suit what I needed. (This is where I wish I could link back to where I found it as I didn’t create this part myself).
The first thing I am doing here is checking to see if the temp table has content or not. If it does not, the job will fire without sending an email which is what I want. I only want to see an email if there is an issue.
The end of my script is a command to drop the temp table.
-- Build HTML DECLARE @bodyHTML varchar(max) -- Check to see if there is anything in the temp table -- If there is, build the HTML for a table in the body of the email IF (SELECT COUNT(*) FROM ##AlertContent) > 0 BEGIN SET @bodyHTML = ' <html> <body> <table border="1"> <tr> <th align="left">Type</th> <th align="left">Title</th> <th align="left">Desc</th> </tr>' + CAST(( SELECT td = AlertType, '', td = AlertTitle, '', td = AlertDesc FROM ##AlertContent FOR XML PATH('tr'),TYPE,ELEMENTS XSINIL ) AS NVARCHAR(MAX)) + ' </table> </body> </html>'; -- Send email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DEFAULT', @subject = @subject, @recipients = @recipient, @body = @bodyHTML, @body_format ='HTML' END -- Drop Temp table DROP TABLE ##AlertContent
When I run this in a query window, this is what I saw. I didn’t include a NOCOUNT command so I see 1 row affected and 2 rows affected because the two insert statements resulted in that in my test data. The Mail queued simple tells me the email worked.
… and here is what the email looked like with my simple example. The “from” address and name are based on my SQL database mail configuration, the subject is from the code above and the table is from the code above.
A real-life example
How I’m using this currently is for monitoring of a nightly set of integrations. I have inserts that are checking my staging tables for errors and inserting them into the temp table. I then have a final insert which populates the contents of a log table added to the temp table. The result is most nights I’m simply getting an email with last night logs because (knock wood) there are no errors most of the time.
I’ve tweaked what I have above slightly so that I alter the subject line based on whether I have errors or not. (I added another variable to hold an ErrorCount numeric value). If I have errors, the subject of my email is “Nightly Summary – Errors” so that I can use Outlook rules to highlight that in my inbox. If I don’t have errors the subject might simply be “Nightly Summary” and it just contains the logs. The Outlook rule is to file that but I know I have it if I want to reference something from a given night.
This may be overly simplistic but the framework for this works well for me. I got tired of getting emails with attachments and having to open it every day just to see if there was an error or not. This is much nicer!
If you want to see more columns of data in the table, you’ll need to do some modifications to the HTML structure to add the extra row and headers. If you’re better at HTML formatting than I am, you can make the table look a lot nicer too!
I don’t know if this will help anyone but I thought I’d share it anyway!