Style over substance

When it comes to managing emails, I can easily receive about 600 a day. This is probably far too many (although that’s a subject for another day). The challenge with receiving so many emails, especially so many system generated errors and warnings is that they can fail to stand out. Important information can be missed. A lack of formatting can also make it difficult to understand what the problem is, if any.

Here is an example of a boring system generated message.

To get around this, I always try to use some pretty HTML in my output just to help with the appearance and to make the message easier to read. I don’t go overboard, just a header tag, something like <h4> is usually enough (unless you want it really big) and of course I like to include my output in a table, usually with a splash of colour in the table header.

Why do I do this? Well a simple change to the colour such as using background-color:#FF0000 can make the content really stand out. Also, if your important alerts are colour coded, the red ones will jump out at you demanding action. This is what I mean by style over substance. I’m not arguing that style is more important than substance, rather that The style makes it easier to consume the substance. On the first email, my server has missed a heartbeat. That is not good. It might have only been a tiny network blip but if not, I have missed an important update – one that needs immediate action.

Here is an example of a noticeable HTML message.

Here you can clearly see what the problem is. There is no extra information to confuse thing. Just a clear header and table of information. IN RED. Notice when I say style, it doesn’t have to be too stylish. It’s not exactly an ultra modern looking email using the latest css. It’s just simple and effective.

But I don’t normally write HTML

HTML is not normally something DBA’s deal with much. The good news is, you don’t need to know it well. You need to know how to update my code and replace my query with yours. Remember that you’ll need a <th> tag to head every column. If you add more columns, the width style needs to total 100% or it can be removed altogether. The rows underneath are auto generated from the query.

[sql]

DECLARE @XDaysOld datetime = GETDATE() – 1
IF EXISTS (SELECT 1 FROM dbo.BackupInfo WHERE BackupFinishDate < @XDaysOld)
BEGIN
DECLARE @EmailRecipient VARCHAR(100), @SubjectText VARCHAR(100), @ProfileName VARCHAR(100), @Body VARCHAR(MAX)
SELECT TOP 1 @EmailRecipient = ‘spam@johnmccormack.it’
SELECT @SubjectText = ‘PRODUCTION Backups are out of date’
SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile

SET @Body =
N'<H4 style=”color:#FF0000; font-family:Arial,Verdana”>DBs listed below have full backups older than 1 days on BACKUPSERVER</H4>’ +
N'<table border=”3″; style=”font-family:Arial,Verdana; text-align:left; font-size:9pt; color:#000033″>’ +

N'<tr style=”text-align:left;”>
<th style=”text-align:left;background-color:#FF0000;color:#FFF;font-weight:bold; width:50%;”>DatabaseName</th>
<th style=”text-align:left;background-color:#FF0000;color:#FFF;font-weight:bold;width:50%;”>BackupFinishDate</th>’
+
CAST ( ( SELECT TOP 10
td = [DatabaseName] , ”,
td = CAST([BackupFinishDate] as NVARCHAR)
FROM dbo.BackupInfo
WHERE BackupFinishDate < @XDaysOld
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @EmailRecipient,
@body_format = ‘HTML’,
@body = @Body,
@subject = @SubjectText;

END

[/sql]

For more info on style, you can visit: http://www.w3schools.com/html/html_styles.asp
For more info on hex codes (colour picker), you can visit: http://www.color-hex.com/