Software

Sending E-mail with Database Mail in SQL Server 2005

In part one of his series, Tim Chapman described how to <a target='blank'href='http://www.techrepublic.com/article/5100-9592_11-6161839.html'>set up Database Mail in SQL Server 2005</a>. Now he explores some of the more advanced functionality of sending Database Mail e-mails, such as formatting your message body as HTML.

As technology continues to advance, the increasing need to know what is occurring in your information systems requires more advanced solutions. It's extremely advantageous to be able to be quickly and easily notified of situations, especially about potential problems; Microsoft continues to produce products that deliver this capability. Some of these database-specific options available in SQL Server 2005 are Query Notifications, Notification Services, Service Broker, and Database Mail.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

Last week, I showed you how to set up Database Mail, the new e-mail messaging platform in SQL Server 2005. At the end of that article, I sent a test e-mail to make sure our Database Mail setup worked correctly. In this article, I will build upon that test e-mail and explore some of the more advanced functionality of sending Database Mail e-mails in SQL Server 2005.

Database Mail options

SQL Server 2005 Database Mail provides several options for sending e-mail messages. These options include sending attachments, setting sensitivity and importance, including query results, and formatting the e-mail message in an HTML format.

To send e-mails in SQL Server 2005, you'll need the script in Listing A to set up a table with some data so you can use our query result options a bit later. The script creates a table and loads some test data.

Sending attachments

The ability to send a file attachment via e-mail is crucial to productivity. The following script will send an e-mail with an attached file named FileAttachment.txt, which is located on my C: drive. You will need to make sure that this file exists so that the procedure does not error.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='Database-mailProfile',
@file_attachments ='C:\FileAttachment.txt';

Sensitivity and importance

It is often necessary to flag e-mail messages as sensitive or imporant to let users know that the message requires care or attention. The following script sends an e-mail with a sensitivity of personal and an imporantance of high. In addition, the script will copy the e-mail address yourname@yourdomain.com on the e-mail message. Note that it is also possible to blind copy users on e-mail messages using Database Mail.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@sensitivity ='Personal',
@importance ='High',
@copy_recipients ='chapman.tim@gmail.com',
@subject ='Message Subject',
@profile_name ='Database-mailProfile';

Query results

One great feature of a database engine housing the ability to send e-mails is the fact that it lets you pass a query to the e-mailing procedure. This feature was available in SQL Mail and remains in Database Mail. A nice new feature in Database Mail is the ability to attach the results from the query as an attachment, which the following script shows. If you do not attach the results as a file, the query results will be placed in the message body. See Listing B.

HTML messages

My favorite new feature of Database Mail is the ability to format your message body as HTML. At first glance, this isn't really that big of a deal, but it does lend itself to some really good possibilites. In the previous example, you sent a query result in the body of the e-mail message. Since the example only included one column of data, it wasn't that unpleasant to look at it. However, when more fields are included in the resultset, formatting goes downhill. The good news is that it is reasonably easy to use some of the new XML capabilities in SQL Server 2005 and some HTML to format your resultset so that it is easy to look at in an e-mail message. The script in Listing C takes a look at how to format an XML/HTML e-mail message using a query result.

When you can send formatted HTML messages, it allows you to embed query results into your query body and gives you the ability to format the query results to be pleasing to the eye. (Although, you could probably argue that the yellow background I am using in the message isn't all that pleasing to view.)

Possibilities

When you are able to send e-mails from the database engine, it opens you up to a lot of possibilities. With the addition of some extra logic and constructs, you can write a homegrown messaging system to ensure that the right people in your organization are "in the know." In part three of my series on Database Mail, I will describe how to write such an application.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

15 comments
slotteredge
slotteredge

Hi, I've created a database mail profile and mail account also updated my smtp server ip and credentials for the same in mail account, changed the port to our smtp port. when I send the test mail...getting following error: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2011-08-31T11:27:32). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required). )

anton.liebscher
anton.liebscher

Found this on a search when it was exactly what I wanted to do. Easy to mod for my purposes. Many thanks for your effort.

anitatripathi
anitatripathi

How to check if the recipients mail address is correct or not. Even if the mail address is wrong the SP returns 0. In SQl server 2000 xp_send_mail, the SP was returning non 0 value if recipients address are incorrect. Please advice.

massagetherapist
massagetherapist

This article is great. However, does anyone know if you can send more than one file attachment in an email? Using @file_attachment or @query_file_attachment?

christian
christian

Hi, Firstly brilliant article, very well received by me especially as this is exqactly what i have been waiting for for quite some time. I do have one question though. Do we know if there is any way to set the sender address on the fly? I.E i have a table that has FromAddress (one of our users) and ToAddress (the person it is sending to) but as far as i can see you have to define the from address when setting up database mail. Any help would be really appreciated.

shefehs
shefehs

We have a problem that all the messages that we send via Database Mail end up caught in our Exchange mail server spam filter. This is true even though the database is within the network. Is there any setting I can change in SQL Server or Database Mail to avoid being flagged as spam? Thanks very much!

rakesh.mishra
rakesh.mishra

Hi, I wanted to know the performance impact of usinf database mail on an OLTP server.Is there any matrix available that can dispay this performace impact? Thnx. Rakesh

gkerr
gkerr

Great article - exactly what I was looking for. I thought using SSIS was the way I needed to go to send query results via HTML formatted email but you steered me in the right direction. This opens up a whole new set of possibilities now. Thanks! Keep 'em coming (please).

oordaz
oordaz

no problem with configuration and email sending but... have you tried with an gmail account??? i can send email from another clients from the same mechine, but not with sql server. any help? :p

andyc209
andyc209

How can i send mail from a database other than the MSDB database that the Database Mail uses? If i try and use another DB with my send mail script it says certain Stored Procs are missing. What do i need to add to get it working from another DB?

thom
thom

I too had Gmail issues. It turned out to be two issues: One issue was with my DNS setup. I had not properly setup my reverse DNS. You need a domain name that is reverse lookup answer for your IP address. This is done in conjunction with your IP provider. Check out http://www.DNSstuff.com/ for help querying this. The second part was my fully qualified domain name. If you have issues finding where this is look on the SMTP server under properties for the domain. You need to set it up so the server sends it out in the handshake with gmail's SMTP server. Actually, this is true with a lot of SMTP servers. If you need help, I would be happy to assist you if I can.

roxy
roxy

Hi... I already configured the database mail but i cannot send a message.... I found an error from DBMAIL logs... Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-03-18T15:18:24). Exception Message: Cannot send mails to mail server. (The operation has timed out.). ) Please help.... I'll really appreciate if you could also email me at roxy@rtgstudio.com.ph Thank you so much..this is very important to me. Best regards, Roxy

amycanada
amycanada

Do you mean that you need to set up an SMTP server (like in IIS) to be able to use Database Mail? I've tried to set up an account with smtp.gmail.com, port 587, with a valid username and password, and checked the 'ssl required' checkbox... And it gives me the same error: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-12-01T08:05:42). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Any help would be appreciated, Thanks

gunderjj
gunderjj

I am also trying to setup database mail to my gmail account for testing purposes. I think I missed something because I get an error : The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2008-06-21T10:49:13). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at ). ) Can you tell me where to go from here? Any help would be great thanks.

Editor's Picks