Today's #TipTuesday is a bit different from my normal type of post. In the course of monitoring some integrations, I wanted to change the emails I get when there are 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 okay 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, that 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 regularly, and if my criteria are 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, that I want the errors in the body of the email, I don't want to have to open an attachment or 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 users can beef it up however they wish to make it fancier! I have all of the pieces put together 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 = 'me@mydomain.com'

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 - 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 when multiple alerts in the same email are needed
-- 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

End Result

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 queue simply tells me the email worked.

SQL result of a query showing that my message has been queued for delivery.

… 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.

Example of an email from the code above.

A real-life example

I'm using this currently for monitoring 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's 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 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 them every day just to see if there was an error or not. This is much nicer!

If I wanted to see more columns of data in the table, I would need to make some modifications to the HTML structure to add the extra rows and headers. Many others will be better at HTML formatting than I am, and 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!