Database Mail

I have a confession to make. Beyond sending a couple of test emails, I've never used xp_sendmail to send email from my database servers. Because I was never comfortable with the idea of a MAPI client on the server, and hated the bother of setting it up, I've always used sp_OA* to send email via CDO (hold the derision, please).

The new mail procedure, sp_send_dbmail, however, works a bit differently. Although we no longer need a MAPI profile on the server, we still have a bit of setup to do. Database mail is disabled by default. The first thing we'll need to do is to enable it. Since it's an advanced option, we'll need to turn on advanced options, first:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE WITH OVERRIDE;

Even though sp_send_dbmail isn't an extended procedure like its predecessor, it still uses extended procedures to handle the mail. This is a bit of a case of “do as we say, not as we do,” since extended procedures are deprecated and Microsoft says we should no longer use them for new development (email might make a good CLR procedure candidate, assuming you don't have to run UNSAFE to access the email server). At any rate, let's enable mail extended procedures:

EXEC sp_configure 'Database Mail XPs', '1';
RECONFIGURE WITH OVERRIDE;

Despite being liberated from the tyranny of the MAPI profile, we still need a mail profile in the database. Even though we have to create a profile, it doesn't require any extra software; it's all in the database and is a convenient way to specify our email account settings as the sender of a message. Books-Online was kind enough to provide a code sample:

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database Mail Profile',
@description = 'Profile with which to send database mail';

Of course, with any legitimate email message, you need an mail account from which to send it. We get to specify several properties for our account. The account name, email address, and email server are some of the more pertinent properties. Don't forget to set your mail server to allow your database server to relay mail:

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Database Mail Account',
@description = 'EMail account for sending database mail',
@email_address = 'mymail@domain',
@replyto_address = 'myemail@domain',
@display_name = 'Inbox Assassin',
@mailserver_name = 'smtp.mymailserver.domain';

Now it's getting a bit tedious. Just one more step and we can actually send an email message. I mentioned before that the profile was a convenient way to specify our email setting for a message. This next procedure associates an email account with a profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account',
@sequence_number = 1;

Assuming we specified a valid email account and smtp server, we should be all set to go. All we have to do to send an email now is to provide our profile, a recipient, a subject, and a message:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail Profile'
, @recipients = 'myemail@domain'
, @subject = 'My first database mail'
, @body = 'Look, ma, No hands!';

Okay... that was fun. Now we have database mail set up on our server, what's next? Well, we could send out thousands of email messages to our close personal friends whose email addresses we store on the corporate database server, but, let's cleanup, instead! To remove our database mail settings, we pretty much just do our setup steps in revers. First we need to disassociate our email account from our profile:

EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account';

Now, we'll get rid of the email account:

EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = 'Database Mail Account';

and get rid of our profile:

EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Database Mail Profile';

Finally, since we want to leave our server just the way we found it, we'll disable the mail extended procedures and turn off advanced options:

EXEC sp_configure 'Database Mail XPs', '0';
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', '0';
RECONFIGURE WITH OVERRIDE;

With a relatively straightforward setup, and no messing with MAPI or sp_OA*, I like database mail enough that I'll be using it for all of my database email messaging needs (all two of them); despite its reliance on extended procedures.