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.