17

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!

Improve this question

2 Answers 2

Reset to default
12

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.

Improve this answer
0
8

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

Improve this answer
1
  • this does not answer the question "sp_send_dbmail stored procedure send with attachment" Commented Jul 19, 2020 at 4:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.