SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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

database-mail

 

Right click on the Database Mail in order to display the context menu and select the Configure Database Mail menu item.

 

configure-database-mail

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

configuration-task

When you click Next

database-mail-feature

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

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.

database-mail-profile

 

database-mail-account

 

 

smtp-accounts

 

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.

 

mail-profile-security

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.
 

configure-system-parameters

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.
 

dbmail-wizard

 

 

process

 

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.

 

database-mail-menu-items

 

 

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

manage-database-mail-accounts-and-profiles

Then select,

view-change-delete-existing-account

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.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems