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.