Sending E-mail with Database Mail in SQL Server 2005

by Tim Chapman | Mar 05, 2007 5:44:00 PM

Tags: Databases, SQL Server 2005 Database Mail, Tim Chapman, Database Mail, e-mail, Microsoft SQL Server, Microsoft SQL Server 2005, database, SQL Server Newsletter

12 comment(s)

Takeaway: In part one of his series, Tim Chapman described how to set up Database Mail in SQL Server 2005. 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.

12 comment(s)