How to configure Database Mail in SQL Server 2008 R2 and Use sp_send_dbmail
The contents of this SQL Server tutorial includes :
What is SQL Database Mail ?
Database Mail Configuration in SQL Server 2008 R2
How to Send eMail using Database Mail sp_send_dbmail T-SQL Command
What is SQL Database Mail ?
The SQL Database Mail is a new enhancement in Microsoft's Data Platform SQL Server with SQL Server 2005 version which enables database applications to send emails from database with SMTP email support.
For more information and details about SQL Server Database Mail you can read the related section What is SQL 2005 Database Mail? at that SQL tutorial.
Database Mail Configuration in SQL Server 2008 R2
Database Mail Setup in SQL Server 2008 is managed by Database Mail Configuration Wizard.
In order to configure database mail in SQL Server 2008 R2, first connect to the related MS SQL Server instance.
The Database Mail tool is in Management folder.
Right click on the Database Mail and open context menu. Click on the Configure Database Mail menu option.
Configure Database Mail option will run the Database Mail Configuration Wizard.
Following the instructions within the Database Mail Configuration Wizard, it is very easy to set up dbmail for your SQL Server 2008 R2.
The Database Mail Configuration Wizard start-up screen message is as follows :
Welcome to Database Mail Configuration Wizard
Database Mail is a SQL Server component that uses the Simple Mail Transfer Protocol (SMTP) to send e-mail messages.
The DBMail Configuration wizard helps you perform setup and maintenance tasks such as:
Managing Database Mail accounts and profiles,
Managing Database Mail security, and
Configuring Database Mail system parameters
Click Next to continue to next step for configuring SQL Server 2008 R2 database mail.
Since we are installing Database Mail for the first time, we will select the setup option as indicated on the configuration step.
Set up option :
Set up Database Mail by performing the following tasks:
1. Create a new e-mail profile and specify its SMTP accounts
2. Specify profile security
3. Configure system parameters
Manage option :
Manage Database Mail accounts and profiles
Security option : (since we are first installing dbmail, this option is disabled)
Manage profile security
Parameters option :
View or change system parameters
Select Set up Database Mail option and click Next button for the next step in the configuration wizard.
TITLE: Microsoft SQL Server Management Studio
------------------------------
The Database Mail feature is not available. Would you like to enable this feature?
In order to enable Database Mail feature click on "Yes" button.
SQL Server administrators and t-sql developers can create management scripts to enable Database Mail for a SQL Server instance.
If you are a SQL Server DBA who wants to enable SQL Server Database Mail by t-sql code, you can execute t-sql sp_configure SQL Server configuration script shown below.
USE Master
GO
sp_configure 'show advanced options', 1
--Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
--Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
GO
reconfigure
GO
sp_configure 'show advanced options', 0
--Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
GO
The run_value of the "Database Mail XPs" SQL Server configuration parameter which is equal to 1 means we could successfully enable Database Mail feature.
System procedure sp_configure for listing and altering SQL Server configuration settings and "Database Mail XPs" indicating the status of Database Mail feature.
The next step in the configuration wizard is the creating new profile step.
SQL Server DBA should supply a descriptive profile name and a description for the newly created dbmail profile.
After the profile name and description are entered, click "Add..." button for adding a database mail account for the related profile.
While creating a new database mail account for SQL Server 2008 R2, specify name, description and other attributes like outgoing mail server (SMTP) properties, and SMTP authentication methods.
As you can see in the above screenshot, besides account name and description, SQL Server administrators should define the email address and SMTP server name where port number is 25 by default.
The three SMTP authentication methods that SQL Server 2008 DBA can choose :
Windows Authentication using Database Engine service credentials
Basic authentication with a given user name and password
Anonymous authentication (which is not a suggested work method with SMTP mail servers)
After the mail account is created, click on OK button to return back to previous New Profile creation screen.
You will see that the new created Database Mail Account is added to the SMTP accounts section of the new profile definition screen.
Press Next button for the next wizard screen Manage Profile Security.
SQL Server administrators can use Manage Profile Security step to configure public profiles and private profiles.
SQL Server database mail profiles can be either public or private profile.
A public profile can be accessed by all users of any mail-host database.
This means public profiles allow any user or any role with access to mail host database, (msdb) to send emails.
On the other hand private profiles are only accessible to specific users or specific roles.
SQL Server administrator can choose a profile as a default profile for public access.
Just select "Yes" in the "Default Profile" dropdown list.
When a public profile is selected as default profile, sql users or roles are able to send email without explicitly specifying the profile.
After configuring public and private profiles continue with the next step by pressing "Next" button on the screen.
The next Database Mail Configuration Wizard screen is "Configure System Parameters" screen.
SQL Server administrators will use "Configure System Parameters" screen to view or change Database Mail system parameters.
The system parameter and its current value are listed for each database mail system parameter.
If you click on a system parameter, a short descriptive information is given at the bottom of the wizard screen.
System parameters :
Account Retry Attempts is the maximum number of times that the mailing process attempts to send email message.
Account Retry Delay is the amount of time (in seconds) for the mail process to wait after each try to send email messages.
Maximum File Size is the maximum size in bytes for an email attachment.
Prohibited Attachment File Extensions can be used to store file extensions which are prohibeted as email attachments as a comma-separated list of extensions.
Database Mail Executable Minimum Lifetime is the minimum amount of time in seconds, that mailing process remains active even there are no messages to be send.
Logging level specifies message types to be stored in Database Mail log system.
Possible logging level values are:
Normal - for only errors
Extended - for errors, warnings, and informational messages
Verbose - for errors, warnings, informational messages, success messages, and additional internal messages.
Default logging level value is Extended logging level.
After database administrators have completed logging level and system parameters configuration, we can navigate to the next screen within the wizard by pressing the "Next" button.
This screen is the administrator varification screen, where users can verify the actions the wizard is about to perform.
After you verify the actions, by pressing the "Finish" button, these actions will be executed on the MS SQL Server 2008 R2 instance.
For the above sample, the wizard will take the following actions:
New Accounts
Create new account 'DBA Mail Account' for SMTP server 'servername'
New Profiles
Create New profile 'General Administration Profile'
Adding Accounts Profiles
Add account 'DBA Mail Account' to profile 'General Administration Profile' with priority '1'
Manage Profile Security
Set 'General Administration Profile' as public profile
Set default profile for 'guest' to 'General Administration Profile'
When the actions on the SQL Server is triggered by the Finish button, SQL Server database administrators can watch the progress on the following screen.
How to Send eMail using Database Mail sp_send_dbmail T-SQL Command
After all required configurations steps are completed for MS SQL Server Database Mail, we can create a test email object and send email via T-SQL code on a Microsoft SQL Server 2008 R2 database instance.
DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
SET @p_profile_name = N'General Administration Profile'
SET @p_recipients = N'database.mail@kodyaz.com' -- TEST
SET @p_subject = N'This is a test mail using sp_send_dbmail'
SET @p_body = 'This is an HTML test email send using <b>sp_send_dbmail</b>.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @p_profile_name,
@recipients = @p_recipients,
@body = @p_body,
@body_format = 'HTML',
@subject = @p_subject
And when the above t-sql script is executed, an email in HTML format will be send to recipients.
The email received by my mailing client MS Outlook can be seen in the below picture.
As last words, I hope I could explain some about how to manage SQL Server 2008 database mail setup and sending emails using sp_send_dbmail stored procedure.