SQL Server : How to Configure Database Mail?

by Ashish Upadhyay

Microsoft introduced SQL Database Mail with SQL Server 2005 as a replacement of SQL Mail used in SQL Server 2000, which uses Simple Mail Transfer Protocol (SMTP) to send email messages. It is a very powerful tool that can send data from your query result set or files from the other resources, even it can send embedded email and attachment, it also allows more flexible approach to managing who sends emails and from what accounts. 

In order to configure Database Mail you need to have a valid SMTP server and valid credentials. In summary , the Account holds the SMTP information , such as email address, mail server name, port number. 

Below  steps will guide you steps by step configuration of Database Mail in SQL Server 2008 or SQL Server 2008 R2:

1. Start the SQL server Management Studio, go to Management à Database Mail, right click and select Configure Database Mail option.

2. Below page will be appear.

3. On click of Next button following screen will appear.

4. Select “Set up Database Mail by performing the following tasks” and click next. If the Database Mail feature is not enable on your Server, following page will appear. 

5. Click Yes to enable the Database Mail feature, now following page will appear. Specify the profile name, description and associated SMTP account information as shown in the following windows

6. On Click of Add button, following page will appear. Provide the SMTP account information I just used my credentials that why erased that. Please use the valid credentials to use in your environment.Click OK.

7. In the next step it will ask whether you want to set this profile as public. If you set this as public then any member under Database Mail role can send email using this profile.

8. Click Next in above window to get the Database Mail System parameters window, where you can configure following parameters

• Account Retry Attempts
• Account Retry Delay (in Seconds)
• Maximum File Size
• Prohibited Attachment File Extension (defaults are exe,dll,vbs, and js)
• Database Mail Executable Minimum Lifetime (in Seconds)
• Logging Level (Normal, Extended and Verbose), default is Extended.

9. You will get the successful configuration message after clicking on next button in above window

10. To test the configuration of Database Mail setup, Click on Send Test E-mail option by right clicking on Database Mail item under Management node. You should be able to receive email on the given email id.

10. Now you can verify the Database Mail log on the following window by right clicking on the Database Mail and then clicking on View Database mail log.

10. Below window will show you the email that has been successfully delivered and/or failed. Log circled in red show the failed attempt while one in green shows the successfully delivered.

Now your server should ve ready to use the Database Mail. You can use the mail profile you created to send an email from Stored Procedure in SQL server.


Configure Email:

As you have already created Database Mail Account and Profile, the next step is to configure the Database Mail. In order to do the same, you need to eable the Database Mail XPs parameter through the  sp_configure  stored procedure. Please run the below script on the master database for the same. 

Use Master

Go

sp_CONFIGURE 'show advanced', 1
Go RECONFIGUREGo sp_CONFIGURE 'Database Mail XPs', 1
Go RECONFIGURE

Go

Send Email:
Now you are ready to send the email through database. Use stored procedure sp_send_dbmail with required parameters. You must remember this stored procedure resides on MSDB database. you can use below script for the same.

USE msdb 
GO 

EXEC sp_send_dbmail @profile_name='Test Profile',
@recipients='testemail@testdomain.com',
@copy_recipients='testemail-1@testdomain.com',
@subject='This is a Test Mail',
@body='Congratulations!!! You have successfully configured your Database Mail.'

Now you should be able to receive email in your mail box. 

SQL Server keep track of the email messages in the following tables 

• sysmail_mailitems --This table keeps track of the email status, Sent_status as 1 - Success, 2 - Failed,     3 --Unsent 
• sysmail_allitems
• sysmail_sentitems  --Email messages with sent_status = 1 in the sysmail_mailitems will have a copy in this table.
• sysmail_unsentitems  --Email messages with sent_status = 3 in the sysmail_mailitems will have a copy in this table. This is pretty much a temporary kind of record.
• sysmail_faileditems  --Email messages with sent_status = 2 in the sysmail_mailitems will have a copy in this table.
• sysmail_log  -- all email log resides in this table.

Below query will help you to access email information sent by SQL Server Database Mail. 

USE msdb

GO

SELECT * FROM sysmail_log  

SELECT * FROM sysmail_mailitems 

SELECT * FROM  sysmail_allitems 

SELECT * FROM  sysmail_sentitems 

SELECT * FROM  sysmail_unsentitems

SELECT * FROM  sysmail_faileditems