How to configure SQL 2005 Database Mail sp_send_dbmail
What is SQL 2005 Database Mail?
Before Microsoft has introduced SQL Server 2005 to database administrators and database developers, administrators and programmers were suffering in their applications just because of the lack of SMTP email support in their SQL Server databases when a need or a requirement occurs to send emails from database.The requirement to send email may occur when a job fails, or in the code you want to send email in your stored procedures or in the sql codes of triggers in certain situations.
Now, Microsoft SQL Server 2005 supports SMTP with a new feature named Database Mail so SQL database administrators do not need to install MS Outlook, or MAPI or any other third party mailing tools to send emails from within the SQL Server database.
Database Mail is just one of the enhancements we were introduced with the new version of SQL Server, SQL Server 2005.
If you are looking for sp_send_dbmail syntax and t-sql sp_send_dbmail examples, please refer to tsql tutorial SQL Server Database Mail sp_send_dbmail Example.
Now we can continue to configuration steps and processes of SQL Server 2005 Database Mail.
Configuration Steps of SQL Server 2005 Database Mail
Right click on the Database Mail in order to display the context menu and select the Configure Database Mail menu item.
Database Mail Configuration Wizard runs and displays a welcome message.
Database Mail Configuration Wizard is used to enable Database Mail, manage security, and configure Database Mail system parameters
When you click Next
TITLE: Microsoft SQL Server Management Studio
------------------------------
The Database Mail feature is not available. Would you like to enable this
feature?
You can enable the Database Mail feature by pressing the "Yes" button or by running the below sql sp_configure mail script
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
Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 0 to 1. Run the
RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the
RECONFIGURE statement to install.
If you run sp_configure command on the master database, you will see that the run_value for the "Database Mail XPs" is changed from 0 to 1 to enable this feature.
sp_configure
First step is creating a new profile. Enter a profile name and a description for the new profile. Then click the Add... button to complete the task.
Click Next button
Use this page to configure a public profile.
Profiles are either public or private. A private profile is accessible only to
specific users or roles. A public profile allows any user or role with access to
the mail host database (msdb) to send e-mail using that profile.
A profile may be a default profile. In this case, users or roles can send e-mail
using the profile without explicitly specifying the profile. If the user or role
sending the e-mail message has a default private profile, Database Mail uses
that profile. If the user or role has no default private profile, sp_send_dbmail
uses the default public profile for the msdb database. If there is no default
private profile for the user or role and no default public profile for the
database, sp_send_dbmail returns an error. Only one profile can be marked as the
default profile
To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.
Use this page to configure a private profile.
Profiles are either public or private. A private profile is accessible only to
specific users or roles. A public profile allows any user or role with access to
the mail host database (msdb) to send e-mail using that profile.
A profile may be a default profile. In this case, users or roles can send e-mail
using the profile without explicitly specifying the profile. If the user or role
sending the e-mail message has a default private profile, Database Mail uses
that profile. If the user or role has no default private profile, sp_send_dbmail
uses the default public profile for the msdb database. If there is no default
private profile for the user or role and no default public profile for the
database, sp_send_dbmail returns an error.
Note:
To send Database Mail users must also be a added to the DatabaseMailUsersRole
database role in the msdb database using Management Studio or sp_addrolemember.
Use this page to specify Database Mail system parameters. View the system
parameters and the current value of each parameter. Select a parameter to view a
short description in the information pane.
Options
Account Retry Attempts
The number of times that the external mail process attempts to send the e-mail
message using each account in the specified profile.
Account Retry Delay (seconds)
The amount of time, in seconds, for the external mail process to wait after it
tries to deliver a message using all accounts in the profile before it attempts
all accounts again.
Maximum File Size (Bytes)
The maximum size of an attachment, in bytes.
Prohibited Attachment File Extensions
A comma-separated list of extensions which cannot be sent as an attachment to an
e-mail message. Click the browse button (...) to add additional extensions.
Database Mail Executable Minimum Lifetime (seconds)
The minimum amount of time, in seconds, that the external mail process remains
active. The process remains active as long as there are e-mails in the Database
Mail queue. This parameter specifies the time the process remains active if
there are no messages to process.
Logging level
Specify which messages are recorded in the Database Mail log. Possible values
are:
Normal - logs only errors
Extended - logs errors, warnings, and informational messages
Verbose - logs errors, warnings, informational messages, success messages, and
additional internal messages. Use verbose logging for troubleshooting.
Default value is Extended.
Reset All
Select this option to reset the values on the page to the default values.
Additional Information about the Retry Options
The sequence number in the Database Mail profile determines the order in which
Database Mail uses accounts in the profile. This is configured by using the Move
Up and Move Down buttons when configuring a profile in the Database Mail
Configuration Wizard. For a new e-mail message, Database Mail starts with the
account that has the lowest sequence number. Should that account fail, Database
Mail uses the account with the next highest sequence number, and so on until
either Database Mail sends the message successfully, or the account with the
highest sequence number fails. If the account with the highest sequence number
fails, the Database Mail pauses attempts to send the mail for the amount of time
configured in the Account Retry Delay parameter of sysmail_configure_sp, then
starts the process of attempting to send the mail again, starting with the
lowest sequence number. Use the Account Retry Attempts parameter of sysmail_configure_sp,
to configure the number of times that the external mail process attempts to send
the e-mail message using each account in the specified profile.
If more than one account exists with the same sequence number, Database Mail
will only use one of those accounts for a given e-mail message. In this case,
Database Mail makes no guarantees as to which of the accounts is used for that
sequence number or that the same account is used from message to message.
Run the below sql command from a query editor.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'recipient@mail.com', @body = 'This is a sample email sent via Database Mail from a t-sql code script', @subject = 'Subject: Database Mail'
"Mail queued." response will be returned.
If you run the sp_send_dbmail stored procedure before enabling the Database Mail feature, you will get a response similar to below.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database
Mail XPs' because this component is turned off as part of the security
configuration for this server. A system administrator can enable the use of 'Database
Mail XPs' by using sp_configure. For more information about enabling 'Database
Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
In such a case use sp_configure to set Database Mail XPs properties or enable the Database Mail feature using the Configure Database Mail Wizard.
If you enabled the database mail but getting the below error message, you might be using a wrong profile name in the sp_send_dbmail command.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
In this case, run the Configure Database Mail Wizard. Then select "Manage
Database Mail accounts and profiles" option in the Select Configuration Task
Then select,
In the manage existing account screen, choose from existing accounts to manage from the dropdown list.
sp_send_dbmail Example T-SQL Code
After SQL Server administrators enable and configure Database Mail sp_send_dbmail can be used in t-sql program codes like stored procedures, triggers, etc.
SQL developers and admins can find tsql example sp_send_dbmail code at t-sql tutorial SQL Server Database Mail sp_send_dbmail Example.
Using sp_send_dbmail, sql developers can send emails to multiple recipients in HTML format.