sp_send_dbmail stored procedure send with attachment

Question

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!

Answer 1

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='[email protected]',
  @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.

Answer 2

Yes you can send the report via HTML format, for example as listed in MS:

Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address [email protected]. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks2008R2.Production.WorkOrder as wo
              JOIN AdventureWorks2008R2.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2006-04-30'
                AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Moreover, you can use the read Sending HTML formatted email in SQL Server using the SSIS Script Task

Also, if you want to schedule HTML report read this here