In the previous two articles, I covered setting up database mail (gmail) and configuring Change Data Capture (CDC), which is the automatic tracking of changes to a table. So, it occurred to me, why not combine these topics and describe how to use automatic email to report changes to a specific table? This could come in handy, right? 🙂 I would like to remind you that this feature is available in the Standard and higher editions of SQL Server. You won’t find it in SQL Server Express.

Creating Email Notification via Database Mail in SQL Server

What will I be doing? In the article about Change Data Capture, I introduced a watchdog monitoring changes to a table with customers. The goal is to create a process that will report all changes summarily to this table via email, which we will send through SQL Server in a clear HTML table format. You can see what a Change Data Capture record for the dimCustomer table looks like in the image below. I will be working with this source.

What interests me:

• Date of change
• Operation type (UPDATE, INSERT, DELETE)
• Number of operations

The message recipient will be the email address biportaldbmail@gmail.com and info@biportal.cz.

Creating HTML Message via T-SQL

Using a script, we will extract the desired information from the Change Data Capture table containing changes to the DimCustomer table (see the Change Data Capture article).

DECLARE @from_lsn binary (10), @to_lsn binary (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_DimCustomer')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
   CAST([tran_begin_time] AS date)                               AS Datum
  ,CASE
     WHEN [__$operation] = 1 THEN 'DELETE !!!!!! Kontrola'
     WHEN [__$operation] = 2 THEN 'INSERT'
     WHEN [__$operation] = 4 THEN 'UPDATE'
   END                                                           AS Operace
,COUNT(*)                                                        AS Pocet_zmen
FROM cdc.fn_cdc_get_all_changes_dbo_DimCustomer (@from_lsn, @to_lsn, 'all') a
     JOIN [AdventureworksDW2016CTP3].[cdc].[lsn_time_mapping] b
        ON a.[__$start_lsn] = [start_lsn]
GROUP BY
   CAST([tran_begin_time] AS DATE)
   ,CASE
      WHEN [__$operation] = 1 THEN 'DELETE !!!!!! Kontrola'
      WHEN [__$operation] = 2 THEN 'INSERT'
      WHEN [__$operation] = 4 THEN 'UPDATE'
    END;

So, the table we will be sending via email looks like this:

Creating an HTML Template (SQL Procedure) and Sending an Email

We will wrap the script from the previous step with logic that creates HTML code. You can find plenty of such examples on the internet. The SQL script is essentially divided into three parts:

• Preparing the source table (as seen in the previous step)
• Preparing the HTML template
• Sending the email using the system procedure sp_send_dbmail
• We will wrap all of this in a procedure, and you can run it, for example, every 5 minutes via SQL Server Agent

SQL Script (Procedure) for Sending an HTML Email:

CREATE PROCEDURE Hlidaci_Pes_DimCustomer
AS
BEGIN

--Příprava zdrojové tabulky

DECLARE @from_lsn binary (10), @to_lsn binary (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_DimCustomer')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
   CAST([tran_begin_time] AS date)                               AS Datum
  ,CASE
     WHEN [__$operation] = 1 THEN 'DELETE !!!!!! Kontrola'
     WHEN [__$operation] = 2 THEN 'INSERT'
     WHEN [__$operation] = 4 THEN 'UPDATE'
   END                                                           AS Operace
,COUNT(*)                                                        AS Pocet_zmen
FROM cdc.fn_cdc_get_all_changes_dbo_DimCustomer (@from_lsn, @to_lsn, 'all') a
     JOIN [AdventureworksDW2016CTP3].[cdc].[lsn_time_mapping] b
        ON a.[__$start_lsn] = [start_lsn]
GROUP BY
   CAST([tran_begin_time] AS DATE)
   ,CASE
      WHEN [__$operation] = 1 THEN 'DELETE !!!!!! Kontrola'
      WHEN [__$operation] = 2 THEN 'INSERT'
      WHEN [__$operation] = 4 THEN 'UPDATE'
    END;

--Vytvoření emailu

 DECLARE @Subject VARCHAR(100), @Nadpis VARCHAR(100), @HTML NVARCHAR(max)

SELECT @Subject = 'Zásahy do tabulky dbo_DimCustomer_CT',
@Nadpis = 'Zásahy do tabulky dbo_DimCustomer_CT'

SET @HTML = '<html><head><style>' +
'td {border: solid black 1px;padding-left:1px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style></head><body>' +
'<div style="margin-top:20px; margin-left:1px; margin-bottom:15px; font-weight:bold; font-size:1.0em; font-family:arial;">' +
@Nadpis + '</div>' +
'<div style="margin-left:50px; font-family:Arial;"><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#17263d>' +
'<td align=left><font face="calibri" color=White><b>Datum</b></font></td>' +
'<td align=left><font face="calibri" color=White><b>Operace</b></font></td>' +
'<td align=left><font face="calibri" color=White><b>Pocet_Zmen</b></font></td></tr>'

DECLARE @body VARCHAR(max)
SELECT @body =
(
SELECT ROW_NUMBER() OVER (ORDER BY Operace) % 2 AS TRRow,
td = Datum,
td = Operace,
td = Pocet_Zmen
FROM Tabulka_k_odeslani
ORDER BY Datum
FOR XML RAW ('tr'), ELEMENTS
)

SET @body = REPLACE(@body, '<td>', '<td align=center><font face="calibri">')
SET @body = REPLACE(@body, '</td>', '</font></td>')
SET @body = REPLACE(@body, '_x0020_', SPACE(1))
SET @body = Replace(@body, '_x003D_', '=')
SET @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#c4dbff>')
SET @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#ffffff>')
SET @body = Replace(@body, '<TRRow>0</TRRow>', '')

SET @HTML = @HTML + @body + '</table></div></body></html>'
SET @HTML = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:1000px;">' + @HTML + '</div>'

--Odeslání emailu přes profil biportaldbmail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'biportaldbmail',
@recipients = 'biportaldbmail@gmail.com; info@biportal.cz',
@body = @HTML,
@subject = @Subject,
@body_format = 'HTML'
END

That’s all there is to it! Now you have an automated process in SQL Server that will send you an email whenever there are changes to the DimCustomer table, summarizing the changes in an HTML table format.

Let me know if you need any further assistance or if you have any other questions!