Database Mail in SQL Server 2008R2

Today I am discussing Database Mail and how to configure Database Mail in SQL Server 2008 R2. This feature is most effective for sending the Email messages using SQL Server. It is very fast and consistent for sending Email by SQL Server and SQL Server jobs with schedules that are based on Simple Mail Transfer Protocol (SMTP). It can hold query results, and can also include files from any resource on your network. It's considered for consistency, scalability, security, and supportability.

For Database Mail configuration, to enable the Database Mail feature you can run the following script:

---------------Start Script----------------------

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

---------End Script ----------------------

Output

After enabling Database Mail you must Create Profile and Account.

Now connect to the SQL Server instance from SSMS and go to Manage Database Mail. Right-click on "Database Mail" and click on "Configure Database Mail".




Click on "Next".



Select the first option "Setup Database Mail by performing..." and move to the next step.



Type in a profile name and profile description. Then click on the "Add" button.



Provide the following details:

Account Name: Desired account name for this SMTP account.

Description: Description of the account

E-mail address: Email account from which emails will be sent. When the recipient receives the email, the sender email is shown as this address.

Display Name: Name associated with the email address

Reply e-mail: where the reply to the emails will be forwarded to.

Server name: Provide the SMTP to be used to send emails from the SQL Server.

Port Number: Port number to be used by this account. Default is 25.

SMTP Authentication: Select the desired authentication to be used.

And then click "OK".



Then click "Next" in the new window.



The above screen shows the public profiles available, then click the "Next" Button.



Then click on the "Next" button.



Then click on the "Finish" button.



Then click on the "Close" button.



You can test the configured Database Mail feature by right-clicking on "Database Mail" and click on "Send Test E-Mail".



Then select the recipient email and click on "Send Test E-Mail".

You can also use the "msdb.dbo.sp_send_dbmail" Procedure in the "msdb" database.

EXEC msdb.dbo.sp_send_dbmail

@profile_name=' DatabaseMail',

@recipients=: '[e-mail]',

@subject='This is Test Mail',

@body='This is the body of the test message.',

@file_attachments ='test\test.txt'