All right dear reader, I am sure you’ve waited long and hard for this moment. So let’s cut to the chase. Here is the gist of Database Mail in Sql Server 2005:

1. Say goodbye to the silly and cumbersome Sql Mail of SQL Server 2000;
2. Database Mail uses SMTP service. You can use Exchange as your SMTP server, if your company uses Exchange;
3. Database Mail needs to be enabled before you can use it. Dah! As part of secure computing, Database Mail is off by default;
4. At a high level, Database Mail needs at least one profile. Each profile needs at least one account. Account is the core of the whole thing, where you define From Address, Reply To address, Account Name, and most importantly, SMTP server name. If your SMTP server does not support anonymous authentication, you will need to supply login and password;
5. Each profile can have more than one account. The idea is that each account is associated with different SMTP servers. If one SMTP server is down, the second account will be used to send mail;
6. You can create more than one profile for different purposes. I think one profile will be enough for a lot of shops;
7. You probably want to create a default public profile, so it can be used by a lot of users;
8. The script below does the following:
a. Enable Database Mail;
b. Create mail accounts. Account name always starts with the server name, so you know where the message is from when you receive it. This script creates 2 accounts. If primary account fails, secondary account will handle the task. For a lot of shops, one account is probably enough. Remove the second account creation if you do not need it;
c. Create a profile;
d. Add the 2 accounts created earlier to the profile;
e. Make the profile a default public profile, so it can be used for general administrative purposes. Having a default public profile also facilitates your migration from xp_sendmail to sp_send_dbmail, because their interfaces are similar.

To verify your script ran successfully, do this:

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'Me@MyCompany.com',
    @body = 'Cool Beans',
    @subject = 'Sweet success' ;

This is actually a lot of stuff. I think I will write an article on it.

Scripts below:

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure 
go
sp_configure 'show advanced options',0
go
reconfigure
go

-- Declare variables for account_name, email_address, domain name, and profile name
declare @PrimaryAccountName varchar(50)
declare @SecondaryAccountName varchar(50)
declare @DomainName varchar(50)
declare @Email varchar(50)
declare @DisplayName varchar(50)
declare @ProfileName varchar(50)

select @PrimaryAccountName = @@servername + ' Public Primary Account'
select @SecondaryAccountName = @@servername + ' Public Secondary Account'
select @DomainName = 'MyCompany.com'
select @Email = @@servername + '@' + @DomainName
select @DisplayName = @@servername + ' Automated Mailer'
select @ProfileName = @@servername + ' Public Profile'
-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = @PrimaryAccountName,
    @description = 'Mail account for use by all database users.',
    @email_address = @Email,
    @display_name = @DisplayName,
    @mailserver_name = 'MyPrimarySMTPServer' ;

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = @SecondaryAccountName,
    @description = 'Mail account for use by all database users.',
    @email_address = @Email,
    @display_name = @DisplayName,
    @mailserver_name = 'MySecondarySMTPServer' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = @ProfileName,
    @description = 'Profile used for general public administrative mail.' ;

-- Add the account to the profile

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


EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @ProfileName,
    @account_name = @SecondaryAccountName,
    @sequence_number =2 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @ProfileName,
    @principal_name = 'public',
    @is_default = 1 ;