Minulé 2 články které předcházely jsem věnoval nastavení database mailu a nastavení change trackingu (CDC) neboli automatického sledování změn nad tabulkou. Tak mě napadá proč tyto témata nespojit – popíšu, jak pomocí automatického emailu odreportovat změny nad určitou tabulkou. to se může hodit ne? 🙂 Připomínám, že tato funkce je dostupná v edici Standard a vyšší. V SQL Server Express ji nenajdete.

Vytvoření emailové notifikace přes database mail v SQL Server

Co budu dělat? V článku o change data capture jsem vytřovil hlídacího psa sledující změny nad tabulkou ze zákazníky. Cílem bude vytvořit proces, který odreportuje všechny změny souhrnně nad touto tabulkou emailem, který si přes SQL Server necháme poslat v přehledné HTML tabulce. Jak vypadá záznam o change data capture nad tabulkou dimCustomer si můžete podívat na obrázku níže. S tímto zdrojem budu pracovat dále.

Zajímá mě:

• Datum změny
• Typ operace (UPADATE, INSERT, DELETE)
• Počet operací

Příjemcem zprávy bude email biportaldbmail zavinac gmail tecka com a info zavinac biportal tecka cz

Vytvoření HTML zprávy pres T-SQL

Pomocí skriptu si vytáhneme požadované informace z change data capture tabulky obsahující změny nad tabulkou DimCustomer (viz článek o change data capture)

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;

Tabulka, kterou budeme posílat mailem vypadá tedy takto:

Vytvoření HTML Template (sql procedura) a odeslání emailu

Skript z předchozího kroku obalíme logikou, která vytvoří HTML kód. Na internetu takových věcí najdete hromady. Já se nechal inspirovat tady: http://www.theboreddba.com/Categories/usefulCode/Nicely-Formatted-HTML-Email-of-SQL-Table.aspx a kód si trošku příspůsobil. SQL script se v podstatě dělí do 3 částí:

• Příprava zdrojové tabulky (viz předchozí krok)
• Příprava HTML template
• Odeslání emailu přes systémovou proceduru sp_send_dbmail
• To celé obalíme procedurou, můžeme např pouštět v 5-ti minutových cyklech přes SQL Server Agent

SQL Script (procedura) pro odeslání HTML emailu:

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

Po spuštění procedury zkontroluju email: