How To send mail with attachment from SQL MI

Question

Hi Team,

I am using msdb.dbo.sp_send_dbmail to send mail from sql mi. I need to send this mail with attachment. Since SQL MI does not support file attachment i am looking for the alternatives. I have one https service like https://test/getfile service which will give an excel file or PDF. I need to send this along with the mail from Proc.

Answer 1

Hi @Radhakrishnan, Yadu Krishnan (Allianz Technology),

Thanks for your information.

From this known thread, we can get the information:

If you 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're 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.

If you have any confuse, please feel free to share us.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications