Setting up Database Mail in SQL Server 2005

SQL Server 2005's Database Mail is completely SMTP based, allowing you to send e-mails with attachments, format HTML e-mails, and more. Tim Chapman fills you in on the advantages of Database Mail and explains how to set it up in your environment.

Database Mail, a new addition to the SQL Server 2005 database engine, is as simple to use as it is useful. Destined to be the replacement for SQL Mail, Database Mail uses a Simple Mail Transfer Protocol (SMTP) server to send e-mails rather than using the MAPI accounts that SQL Mail required. This allows your organization to send e-mails with attachments, e-mail query results, attach query results, and format HTML e-mails. It also gives you the ability to set many other configuration settings without requiring you to have an Exchange Server or configuring any type of MAPI workaround.

The advantages of 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!

Besides being totally SMTP based, Database Mail has many other advantages:

  • It runs outside of the database engine, so the stress placed on the database engine is minimal.
  • It is cluster aware and fully supported in a clustered environment.
  • Its profiles allow for the redundant use of SMTP servers. (I go into more detail about this later in the article.)
  • It allows you to send a query text as a parameter to the stored procedure, which will execute the query and send the results in the e-mail.
  • The messages are sent asynchronously via a Service Broker queue, so you will not have to wait for a response when sending an e-mail.
  • It has multiple security features for sending e-mail, such as a filter for what attachment extensions can be sent and a governor for attachment size.

Setting up and using Database Mail

A bit of planning is required before you can set up a Database Mail solution. First, you need to have an available SMTP server so that e-mails can be sent. If you do not have an available SMTP server, read Microsoft Knowledge Base article 308161 for tips on setting one up. If you are not sure whether your organization has an SMTP server, talk to your network administrator to obtain the machine name or the IP address of the server. Your network administrator may need to configure the server so that e-mails can be sent from your SQL Server machine.

In Database Mail, the Account holds information that the database engine uses to send e-mail messages. An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

To send a Database Mail e-mail, a Profile must be used. A Profile is set up of one or more Accounts. This Profile-Account setup is very useful for a couple of reasons. It allows you to associate more than one Account to a profile, which means that you can associate more than one e-mail server to a profile. So, when you try to send an e-mail, each Account for the profile is tried until the message is successfully sent, which is great in case one or more of your SMTP servers is unavailable. It also allows you to develop your application code for sending e-mails without worrying about changing the Profile name for different environments. You can use the same Profile name for your Development and Production environments; the only difference is that the Accounts contained in the profiles will be different.

It is time to take a look at how to set up a Database Mail account. For our example, I will assume that you are sitting in front of a development machine for which you have sysadmin access. If you are not, you will need to be a member of the DatabaseMailUserRole in the msdb database.

The following script sets up some variables that I will use throughout the example. Note that the entire script will be run in the context of the msdb database, where Database Mail objects are stored.

USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddressVARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

Here I am setting up our ProfileName, AccountName, STMP server name, and the name that will display in the From field in the e-mail.

SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';
SET @DisplayUser = 'The Mail Man';

The script in Listing A does some clean up work, so if I run the script again, I won't have to worry about errors.

The following section adds our Account, Profile, and Profile-Account association to the system.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

Now that everything is set up, I will send a test e-mail.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName

To see if the message was sent successfully, I can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems

What's next

In my next article, I'll walk you through the different options for sending e-mails using Database Mail in SQL Server 2005.

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.

By Tim Chapman

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.