Sql-server – sp_send_dbmail stored procedure send with attachment

database-mailsql serverstored-proceduresxml

I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the customer as an attachment.

I'm looking for a more permanent solution, so I intend on using sp_send_dbmail stored procedure to run the query and send it as an attachment.

Everything works but the formatting of the message. Initially I tried to attach the output as a CSV file with a @query_result_seperator = ',' but the results were everywhere!

When I run the report normally, the output looks fine in SQL. But sending it as a CSV or just in the message body doesn't.

I think it might work better if I export the output as HTML and send that as an attachment/or as XML but I don't know how to do this.

Does anyone have any suggestions?

Thanks in advance!

Best Answer

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server.

Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is:

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

You would then attach the file to the email in sp_send_dbmail.

USE msdb;
GO

EXEC sp_send_dbmail 
  @recipients='mark@mark.com',
  @subject='Client Report',
  @body='Please find your latest report attached',
  @file_attachments='D:\MyTable.csv';

You could, if you want, attach multiple files to one email.

Method 1

For each error condition, create an inline, table-valued user-defined function which generates the erroneous results.
Make a table which contains all of the function names, with a field for the email recipients.
Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the email recipients from the table and execute msdb.dbo.sp_send_dbmail, sending in 'SELECT * FROM ' + FunctionName + '();' as the query for the attachment.
Build a procedure (MailErrorResultsAll) which uses a cursor to loop over the table with the function names. In the loop, you will execute dynamic SQL along these lines:

'IF EXISTS(SELECT * FROM ' + FunctionName + '()) BEGIN EXEC MailErrorResults @FunctionName = ' + @FunctionName + '; END'

(Beware of SQL injection attacks possible by adjusting the FunctionName values in the table.)

You could omit building the separate procedure MailErrorResults, but then you are writing the entire msdb.dbo.sp_send_dbmail call in dynamic SQL, which is ugly and error prone.

You could avoid MailErrorResults hitting the function table again by sending it everything it needs (e.g. the recipients), but your dynamic SQL in MailErrorResultsAll would become more complicated, and the function table will likely be too small for this to matter. This is especially true if you end up having more information in the function table, such as a field describing the problem along with information on how to fix it.

The advantages of this method are that (1) the SQL is only written once; (2) the error detection SQL can be written using validation (no dynamic SQL quotation mark issues); (3) a reusable execution plan can be generated; and (4) eventually the check can be embedded into your application logic. The disadvantage is that the query is run twice for each check-- to see if there are errors and then during the error sending process. That disadvantage can be mitigated with Method 2.

Method 2

You would essentially reproduce Method 1, but you could eliminate the double call to the function by creating a permanent table to store the error results. Your dynamic SQL would look something like this:

'INSERT INTO ErrorList (ErrorRunNumber, a, b, c) SELECT ' + CONVERT(varchar(50),@ErrorRunNumber) + ', a, b, c FROM ' + @FunctionName + '();'

You would have a separate table called ErrorRuns which would store each execution of this process for a particular function. You wouldn't need two procedures. Your single procedure would loop over the function table, running the above dynamic SQL for each function (getting a separate run number for each, of course), and then your msdb.dbo.sp_send_dbmail call would be passed a query like this:

'SELECT a, b, c FROM ErrorList WHERE ErrorRunNumber = ' + CONVERT(varchar(50),@ErrorRunNumber) + ';'

This trades storage space and extra writes for the double reads of the existence check in Method 1. This could be superior if your error detection is resource intensive.

Method 3

One other option, which I don't recommend, is to simply put the raw SQL statements into a functions table. You could then pass them into dynamic SQL without worrying about the single quote issues, because they could be retrieved from the table as native strings. I would only explore this if you had an aversion to user-defined functions for some reason.

For example, if your end users were really writing the queries themselves and did not have user-defined function creation rights, you might have to explore this.

Another twist to this method would be to have the procedure responsible for saving the user-specific SQL execute dynamic SQL to create the user-defined function, and then use one of the methods above.

The easier you make it for end users to execute their own SQL in your environment, though, the more security concerns you will have.

Best Answer

Related Solutions

Sql-server – Stored procedure to validate xml content against dtd

Sql-server – Error checking with sp_send_dbmail

Method 1

Method 2

Method 3

Related Question