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.
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.
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.
@subject ='Message Subject',
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 email@example.com on the e-mail message. Note that it is also possible to blind copy users on e-mail messages using Database Mail.
@subject ='Message Subject',
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.
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.)
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 firstname.lastname@example.org.
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.