Formatting HTML emails in SQL

When I am writing monitoring/alerting stored procedures for my SQL Servers, its important to have those alerts be well-formatted. I like to minimize the amount of time I spend sorting through emails & alerts, and part of that is making sure that I can determine the urgency & priority of an email alert at a mere glance. More importantly, it has to be equally useful to me at my desk & on my mobile.

Some email alerts have little content beyond the subject. Other times, there might be query results (a list of error messages, maybe) that are best formatted into an HTML table.

I won’t go into all the scenarios when formatting makes sense. Nor will I go on a tangent about how important effective communication is. Nor will I stand on my soapbox talking about how spending some extra time on writing an email is time well-spent.

I’m just here with a tip to make those well-formatted emails easier & more consistent.

Create a function

It’s so simple & obvious, it took me years to think of it: create a function.

Yep, that’s it….and a super simple function at that. The function should return a <style> tag containing standard CSS in the form of nvarchar(max).

How complicated you make the function is up to you. I avoid using a parameter that would vary the output in favor of one consistent simple output. We’re not sending marketing emails, we’re just trying to keep our alerts clear & concise–don’t over complicate it. Do spend some time getting the CSS right so that emails look good in Outlook* and on your mobile phone.

I create this in my DBA database (ie, the utility database that contains my bag of tricks and exists on every server I manage).

Check out the function I use, but there’s not much to it. The big win is not the code itself, it’s the ease and consistency of using CSS in all your email templates.

An interesting tangent

Outlook 2007/2010/2013 uses Word as the HTML rending engine. There are quite a few CSS options that aren’t supported. In my example code, you’ll notice I define style for “tr:nth-child(even)”. In Outlook, this option isn’t supported, even though it is supported on all current-generation browsers. You’ll be a little luckier if you use Outlook 2011 for Mac. It’s the only version of Outlook that uses WebKit for HTML rendering, so it will render most CSS properly. Why does Microsoft make it’s software work better on Mac than on Windows? One of life’s little mysteries.

1 Trackback / Pingback

  1. Alerting on SQL Server Blocking - Andy M Mallon - AM²

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.