sp_send_dbmail instellen, inclusief bijlage!

Ik moet een wekelijks rapport opzetten voor een van mijn klanten. Je kunt het rapport met de hand draaien door een T-SQL-statement uit te voeren en het resultaat naar Excel te kopiëren. Na het kopiëren gaat het per e-mail naar de klant. Met de bijlage-opties van sp_send_dbmail kun je dit proces automatiseren.

Met de hand een query draaien en die naar Excel kopiëren is niet de beste en meest efficiënte manier om dit te doen. Je kunt bijvoorbeeld Reporting Services installeren, een rapport maken en een subscription inplannen. Je kunt ook een stored procedure uitvoeren, de resultaten in een bijlage opslaan en die mailen. Met een paar stappen automatiseer je het proces: de sp_send_dbmail stored procedure draait de query en verstuurt het resultaat als bijlage.

Moet je een bestand exporteren en als bijlage versturen, dan kun je dat in SQL Server volledig automatiseren. Eerst maak je een bestand van een dataset. Dat doe je met het bulkcopy-commando. Bulkcopy is geen SQL-commando maar een commando voor de command prompt. Je kunt het dus niet direct uitvoeren in SQL Server Management Studio (SSMS). Je kunt het wel in je code opnemen en xp_cmdshell gebruiken om bcp-commando’s te draaien.

Hoe exporteer je SQL-resultaten naar CSV?

Voordat je een queryresultaat naar een e-mailadres kunt sturen, moet je eerst het queryresultaat naar een CSV-bestand exporteren. Dat gaat het makkelijkst met bulkcopy.

Ik laat zien hoe je bulkcopy gebruikt. Start de command prompt: druk op de Windows-knop, kies run en typ “CMD”. De command prompt gaat open. Typ dan “bcp”. Je scherm ziet er zo uit:

Zoals je op het scherm ziet, kun je bulkcopy met veel variabelen gebruiken. De basissyntax voor bcp ziet er ongeveer zo uit:

1
bcp "SELECT Column1,Column2,Column200 FROM DataBase.dbo.Table" queryout "D:\ExportTable.csv" -c -t , -S SERVERNAME -T

Nu geef ik je een voorbeeld van hoe je deze data exporteert:

Tabel: DimDatum
Query: SELECT * FROM DimDatum WHERE Jaar = 2016
Exportpad: c:\Temp\ExportTable.csv
SQL-instance: SERVER01

1
bcp "SELECT * FROM DWH_NL_DMSA_1.dbo.DimDatum WHERE Jaar=2016" queryout "C:\Temp\ExportTable.csv" -c -t , -S SERVER01 -T

Zoals je ziet werkt dit, cool! Ik heb alle dagen van het jaar 2016 geëxporteerd naar een CSV-bestand in C:\Temp met de naam “ExportTable”.

Nu wil ik zien wat er in het CSV-bestand staat. Als je dubbelklikt op ExportTable.csv, gaat Excel open. Dat ziet er niet erg cool uit:

Het ziet er zo beroerd uit omdat het bestand een CSV-bestand is met een “,” als scheidingsteken. Wil je de data in een normale vorm bekijken, dan kun je de data in Excel importeren.

Zoals je ziet, staan er geen kolomkoppen in het CSV-bestand. Met een trucje kun je koppen meenemen in het bcp-commando. Het makkelijkst is de queryout-optie te gebruiken en met union all een lijst met kolomnamen te koppelen aan de echte inhoud van de tabel.

Voorbeeld-SQL

1
SELECT 'DatumID', 'DatumWaarde' UNION ALL SELECT CAST(DatumID AS VARCHAR(8)), CAST(DatumWaarde AS VARCHAR(32)) FROM DWH_NL_DMSA_1.dbo.DimDatum WHERE Jaar=2016

Nu kun je deze SQL in het BCP-commando opnemen. Let op de cast: die is nodig omdat de koppen tekst zijn. Je moet dus elke kolom naar een varchar casten.

1
bcp "SELECT 'DatumID', 'DatumWaarde' UNION ALL SELECT CAST(DatumID AS VARCHAR(8)), CAST(DatumWaarde AS VARCHAR(32)) FROM DWH_NL_DMSA_1.dbo.DimDatum WHERE Jaar=2016" queryout "c:\Temp\ExportTable.csv" -c -t, -S MSINTFR07666.emea.adecco.net\BENL_DWH_1 -T

Draai nu het commando en je ziet dat er een kopregel in het CSV-bestand staat :-)

Nu weet je hoe je met bulkcopy (bcp) een CSV-bestand maakt van een SQL-commando. Je kunt het bcp-commando opnemen in je eigen code of stored procedure. Je moet xp_cmdshell aanzetten om de command prompt in je SQL-code te gebruiken.

Mogelijke fout:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

Deze fout krijg je als je een locatie opgeeft die niet bestaat. In mijn voorbeeld schrijf ik het bestand naar c:\Temp\. Bestaat die locatie niet, dan krijg ik de fout.

sp_send_dbmail-bijlage instellen

In de vorige stap heb je geleerd hoe je data uit SQL Server naar een CSV-bestand exporteert. Nu wil je weten hoe je het bestand naar een e-mailadres stuurt. SQL Server gebruikt de stored procedure sp_send_dbmail om e-mails te versturen. Dit commando is erg handig als je een databasefreak bent. Je kunt het gebruiken voor foutafhandeling of om bestanden naar klanten te sturen.

Voordat je e-mails vanuit SQL Server kunt versturen, moet je eerst Database Mail instellen. Daar schrijf ik geen tutorial over, want je kunt het hier lezen.

Syntax van sp_send_dbmail met een bijlage

1
USE msdb
2
EXEC sp_send_dbmail
3
@profile_name='DbMailProfile_NoReply',
4
@recipients='info@sqlblog.nl',
5
@subject='Exporttable Attachment',
6
@body='Hi dude! Here is the ExportFile from SQLBlog.nl. Greets, SQLBlog.',
7
@file_attachments='C:\temp\ExportTable.csv'

Als je deze SQL draait, zie je dat de outputtekst “Mail queued.” is. Let op: de parameter file_attachments moet een pad op de SQL SERVER zijn, niet op je pc waarop SQL Server Management Studio draait.

Wil je meer dan één bijlage toevoegen, dan zet je een ; tussen de paden in de parameter @file_attachments. Bijvoorbeeld:

1
@file_attachments='C:\temp\ExportTable.csv;C:\temp\ExportTable2.csv;C:\temp\ExportTable3.csv'

Veel plezier met deze coole SQL-functie! Je kunt er echt coole procedures omheen bouwen, wees creatief!