Setup sp_send_dbmail attachment included!

I need to setup a weekly report for one of my customers. The report can be manually run by executing a T-SQL statement and copy the result to Excel. After copying, it’s emailed to the client. By using sp_send_dbmail attachment options, you can automate this process.

Manually running a query and copying it to excel is not the best and efficient way to accomplish this task. You can install Reporting Services for example, create a report an schedule a subscription but you can also execute a stored procedure, save the results in an attachment an mail it. With some steps to do, you can automate the process by using the sp_send_dbmail stored procedure to run the query and send it as an attachment.

If you need to export a file and send it as an attachment, this can be fully automated in SQL Server. First, you need to create a file from a dataset. This can be done using the bulkcopy command. Bulkcopy is not a SQL command but a command prompt command so you cannot execute this directly in SQL Server Management Studio (SSMS). You can integrate it in your code and use xp_cmdshell to run bcp commands.

How to export SQL results to CSV?

The first step you must do before you can send a query result to an email adress is to export the qeury result to a CSV file. The easiest way to do this is by using bulkcopy.

I will show you how you can use bulkcopy. Start the command prompt by pressing the windows button, run, “CMD”. This will open the command prompt. Then press “bcp”. Your screen will look like this:

As you can see on the screen, bulkcopy can be used with a lot of variables. The main syntax for bcp is something like this:

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

Now I will give you an example how to export this data:

Table: DimDatum
Query: SELECT * FROM DimDatum WHERE Jaar = 2016
Export path: c:\Temp\ExportTable.csv
SQL Instance: SERVER01

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

As you can see, this works, cool! I have exported all days of the year 2016 to a CSV file in C:\Temp called “ExportTable”.

No I want to check what’s in the CSV file. When you double click on the ExportTable.csv, excel will open. This doesn’t look very cool:

The reason it looks so sucky is because the file is a delimited csv file with a “,” as delimiter. If you want to check the data in a normal format, you can import the data in excel.

As you can see, there are no headers included in the csv file. You can include headers with the bcp command by using a kind of trick. The easiest is to use the queryout option and use union all to link a column list with the actual table content.

Example SQL

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

Now you can include this SQL in the BCP command. Take care about the cast command, this is needed because the headers are text so you need to cast each column to a varchar.

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

Now you can run the command and you will notice there is a header in the csv file 🙂

Now you understand how you can create a csv file from a sql command using bulkcopy (bcp). You can include the bcp command in your own code or stored procedure. You need to enable the xp_cmdshell to use the command prompt in your sql code.

Possible error:

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

This is caused when you set a location that doesn’t exist. In my example, I use c:\Temp\ to write the file. When this location doesn’t exist, I get the error.

Setup sp_send_dbmail attachment

You have learned how you can export data from SQL Server to a csv file in the previous step. Now you want to know how you can send the file to an email address. SQL Server uses the sp_send_dbmail stored procedure to send emails. This command is very handy if you are a database freak. You can use the command for error handling or sending files to clients.

The first step before you can send emails from SQL server is setting up database mail. I am not going to write a tutorial because you can read it here.

Syntax sp_send_dbmail with an attachment

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

When you run this SQL, you will notice that the output text is “Mail queued.” Keep in mind that the file_attachments parameter should be a path on the SQL SERVER, not your pc running SQL Server Management Studio.

If you want to add more then one attachment, you must add a ;  between the paths in the @file_attachments parameters. For example:

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

Have fun using this cool SQL Function! You can build really cool procedures around it, be creative!