Send Email From SQL Server Database(Configuration and sending)

In this article I would show how to setup the Database Mail which is used to send the Email using SQL Server. Database mail is the replacement of the SQLMail with many improvements. So we should upgrade to the Database Mail.

In order to configure mail using Database Mail in SQL Server, there are three main steps that need to be carried out.

1) Create Profile and Account

2) Configure Email

3) Send Email.

1) Create Profile and Account:

First we need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail option in Management option. This wizard is used to manage accounts, profiles, and Database Mail global settings as shown below:

Open the Database mail configuration wizard -

Enter profile details to be created


Add/Manage mail account



Manage Profile Security settings

Verify/Specify Parameters


Wizard Completion
Wizard Finish/Success

2) Configure Email:

After the Account and the Profile have been created successfully, we need to configure the Database Mail. For this, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

After all configurations are done, we are ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='test',
@recipients='test@gmail.com',
@subject='Test message',
@body='This is the body of the test message.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker.

Database Mail keeps track of outgoing e-mail messages in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems .

The status of the sent mail can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table