Database Mail - Send Emails from SQL Server

Today, we will discuss how to configure and send emails using SQL Server 2008. Although the setup process is more or less same in both SQL 2005 and SQL 2008. Prior to SQL Server 2005, the database mail was called SQL Mail and lacked a few features that Microsoft introduced in database Mail.

Before you can send emails using SQL Server, you must first create a profile to use when sending emails. Below are step by step instructions on configuring database mail using management studio.

1. Expand Management and right click on Database Mail

2. Click on Next

3. Select the first option "Set up Database Mail by performing the following tasks".

4. Enter a profile name and click on Add. Note, since you won't have a profile setup, you will see the screen in step 5 below.

5. This is where you will enter the SMTP server information. In theory, if you are running IIS on the Database Server, you can configure IIS - SMTP Mail (IIS 7.x no longer have this feature, although there are still ways to configure it for now, but I expect this to go away soon). Whether you should run IIS on database server or not is a whole another argument.

Alternatively, you can configure SMTP server on another machine in your network and use it to send emails from both SQL Server via database mail and also from other applications.

6. Click on OK and click on Next, which will show you the following screen. You can leave defaults in most cases, but you can change the file attachments that you want to prohibit here.

7. Click on Next and click on Finish. Now Database mail is configured and ready to be used. You can right click on Database Mail again and click on Send Test Email to send a test email.

Before you send test email, make sure SQL Server is configured to send emails. Run the following script.


sp_CONFIGURE 'Show Advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs',1
GO
RECONFIGURE
GO


Before you can use the database mail to send emails or alerts from SQL Agent jobs, you need to add operators.

8. Right click on Operators from the Management Studio.

9. Setup one or more Email Operators

10. Now you are ready to configure a SQL agent job to send an alert or a notification email based on an action.  I will not go through creating a SQL agent job in this post, but let's assume you have a job that you want to send you an alert or an email when it fails. First, we will configure an alert. Right click the job and click on properties. Click on Alerts.

11. You can pick an operator that you created below and the type of alert you want to send. Response and Options allows you to configure additional options.

12. Now lets setup Notification. Click on OK from the Alerts screen to go back to the main screen and click on Notifications. Here you should be able to select the email or page that you want to send and the condition along with the operator you want to send it to.

Click on OK, and you have configured database mail to work with your SQL agent job.

You can also use "sp_send_dbmail" to send an email using SQL script

USE msdb
GO
EXEC sp_send_dbmail @profile_name='YourProfile', @recipients='youremail@email.com',
@subject='Test',@Body='Test Email'

SQL Server stores emails in the following system tables in msdb database. You can run select statements on these tables to check if an email has been successfully processed or failed.

sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems.

sysmail_mailitems has the initial emails and when an email is sent or failed, the sent_status flag is updated and also the email is copied into the appropriate table.

You can also check the log of all the emails in sysmail_log table.

Database Mail is a powerful feature and you can use it to ensure you are getting notified when a critical job didn't execute as it should have.

As always, your comments are welcome.