T-SQL Tuesday #005: Cleanup Your Notification Emails With HTML Tables

Introduction

I must admit this post is more along the lines of the “snack” series that (Andy Leonoard|Blog|Twitter) posts, but short-and to the point has some value!  I am writing this one to weigh in on this month’s #TSQL2sday topic about reporting.  So to blatantly steal the “snack” term from Andy, here it is:

TSQL Snack:

As a result of your load process, you send an email to your users to notify them that new data is available….right?  Well you should.  You can send a simple text email saying things are done, which certainly works, or, you can add some value and interest by providing some counts of what was loaded.  (You are auditing your loads…. that is another topic)  Your audit table already has everything you need, and a simple HTML table will get you professional looking output.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'<H1>Warehouse Load Completion</H1>' +

    N'<table border="1">' +

    N'<tr><th>End Date</th><th>Table</th>' +

    N'<th>Rows Loaded</th>' +

    CAST ( ( SELECT td = CONVERT(VARCHAR,exec_start_dt, 110),       '',

                    td = tbl_name, '',

                    td = ISNULL(final_row_cnt - initial_row_cnt, 0)

              FROM dw_dbo.ld_audit

              WHERE audit_key >= (SELECT MAX(audit_key)

                                  FROM dw_dbo.ld_audit

                                  WHERE tbl_name = 'Master')

              AND tbl_name NOT IN ('Step','Datamart')

              ORDER BY (final_row_cnt - initial_row_cnt) DESC, audit_key

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'General Notifications',

                             @recipients = 'Group-Distribution-List@your-domain.com; individual@your-domain.com; oncall-email@your-domain.com;',

                             @subject = 'Data Warehouse Load Complete',

                             @body = @tableHTML,

              @body_format = 'HTML';
				

  This example reads all of the audit rows, calculates the number of rows loaded for everything that was loaded since the start of the last “master” (which is my driver) audit row.  The output is put into a HTML formatted table (does not matter now many rows come back), gets a header-title, and column titles, and is sent to a set of distribution lists and email names via dbmail. What you will get is something like this (obscured my production names):

Conclusion

Communication is a good thing!  You should be letting your users know when loads are complete, what kind of data has been loaded, and what to expect.  Since SQL Server 2005/2008 make the HTML tables so simple, there is no reason that those notifications should look “simple”.

Because it’s TSQL2sDay

I know this is not what my blog is about… but a poll seems like the thing to do for today’s topic.

Tags: ,

4 Comments on “T-SQL Tuesday #005: Cleanup Your Notification Emails With HTML Tables”

  1. Ramdas April 15, 2010 at 3:50 pm #

    Really neat way of sending out events/alerts in e-mail.
    Thank you

Trackbacks/Pingbacks

  1. T-SQL Tuesday #005 – Reporting – The Round-Up – SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V - April 15, 2010

    […] Stef Bauer shows us an easy way to setup an HTML table for presenting a cleaner list of events in an email. […]

  2. SQL Server 2005/2008/2008 R2 Performance Monitoring « Preston's Development - November 3, 2012

    […] https://stef-bauer.com/2010/04/13/t-sql-tuesday-005-cleanup-your-notification-emails-with-html-tables… […]

  3. SQL Server 2005/2008/2008 R2 Performance Monitoring | Question Driven - April 18, 2013

    […] https://stef-bauer.com/2010/04/13/t-sql-tuesday-005-cleanup-your-notification-emails-with-html-tables… […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: