How To send mail with attachment from SQL MI

2024-05-17T08:52:23.3666667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,100 questions
{count} votes

1 answer

Sort by: Most helpful
  1. LucyChenMSFT-4874 2,485 Reputation points
    2024-05-20T01:41:28.3266667+00:00

    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