Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

SQL Database Mail - Send Emails with Attachment

SQL Database Mail - Send Emails with Attachment

Rate This
  • Comments 7

Database Mail allows you to attach files in emails.   Several examples are provided to show how you can do it.

The article here is assumed that a SQL Database Mail profile "MailProfile1' has been created.  The MailProfile1 tells Database Mail what account it should use to send emails.  For how to create a Database Mail profile, you can refer to the previous post  SQL Database Mail.

Example 1 - Attach a single file in Database Mail

-- Start T-SQL
USE msdb
EXEC sp_send_dbmail
  @profile_name='MailProfile1',
  @recipients='someone@microsoft.com',
  @subject='Test Attachment',
  @body='An attachement has been included in this email.',
  @file_attachments='D:\temp\blog.jpg'
-- End T-SQL

This script will attach the file from "D:\temp\bloc.jpg" and send out the email.   After sp_send_dbmail is run, the person at @recipients may receive an email like the following one.

Example 2 - Attach multiple files in Database Mail

-- Start T-SQL
USE msdb
EXEC sp_send_dbmail
  @profile_name='MailProfile1',
  @recipients='someone@microsoft.com',
  @subject='Test Attachment with Two Files',
  @body='Two files have been included in this email.',
  @file_attachments='D:\temp\blog.jpg;D:\temp\long folder\sample.txt'
-- End T-SQL

This script will attach two files from "D:\temp\bloc.jpg" and "D:\temp\long folder\sample.txt".   After sp_send_dbmail is run, the email may look like below.

 

The multiple files must be separated by semicolon (;).  It is fine to have spaces in the file path as long as the semicolon can delimit each path.

The size of attachment is limited to 1 MB per file.  You can change it in Database Mail configuration as shown below.

The Maximum File Size (Bytes) is the size limitation of attachment.   You probably have noticed another item "Prohibited Attachment File Extensions" that is just below the line of Maximum File Size (Bytes).   As the name suggests, files with the prohibited extension such as ".exe" can not be sent by DatabaseMail.  If you want to send it anyway, you can rename the extension to something else, e.g., rename "foo.exe" to "foo.txt", then send it out.

For more information, see the sp_send_dbmail in MSDN document.

<End of Post>

 

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
  • Useful article :)

  • And if I need file name in attachment to be formed dynamically, for example based on date now?

    How I can do this?

    Thanks

  • Thx............for help

    :)

  • Thanks for sharing!!!

  • It is very helpful. I have 2 doubts here:

    1. What I need to add many files, say 10+?

    2. How do I fetch a file in some folder and then add this file as a attachment and a email?

  • thnx

  • thnx

Page 1 of 1 (7 items)