MS SQL Server - Configure / Send Emails by Database Mail

This post is written by following SQL 2008 R2.

MS SQL Server have the feature to send emails. Let see how we can start using it:

There we have a Stored Prcocedure named msdb.dbo.sp_send_dbmail used to send emails. But if you call this SP straight away like:

EXEC msdb.dbo.sp_send_dbmail  
         @recipients    = 'myID@yahoo.com'
        ,@subject        = 'Test Subject'
        ,@body            = 'Test Body'
        ,@body_format    = 'HTML' 

Enable Database Mail XPs

After running the above stored procedure, you may get this error:

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.

This is because first you have to configure your server for sending emails. To fix this error, you can connect to SQL Server Instance with System Administrator (SA) Privileges and execute this script to enable:

USE MASTER
GO

--Enable 'Show Advanced Options'
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

-- Enable Database Mail XPs Advanced Options in SQL Server */
SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO

--Diable 'Show Advanced Options'
SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Now after this if you try to run the same Stored Procedure to send your email, this time you might get this error:

No global profile is configured. Specify a profile name in the @profile_name parameter.

Configure Database Mail Profile Account

If you have not already configured Database Mail Profile Account, follow these steps to create a new one:

1. Go to SQL Server Object Explorer.
2. Expand Management Node.
3. Right click on Database Mail and select Configure Database Mail and proceed with the wizard.
    
   
   
   
   
   
   
   
   
4. From Select Configuration Task screen, select Set up Database Mail by performing the following tasks.
   
5. Put your Profile name and Description, and in SMTP accounts grid, select Add to set up a new SMTP account.
6. A popup box will appear, click on New Account.
   
7. Now  provide all details for your SMTP account setup. I have setup with my yahoo account. If you use yahoo mail service, you have to provide following details for yahoo:

Server name: smtp.mail.yahoo.com
Port number: 587
And mark the checkbox for Secure Connection (SSL).

      

       Rest of the things you provide your email id with password.
       Click Next and Finish the wizard.

Now you have setup this new SMTP account, then second thing you have to do is to Enable Database Mail XPs as described below.

Configure a Default or Global Database Mail profile

If you have already configured Database Mail Profile Account then you have to follow these steps:

1. Go to SQL Server Object Explorer.
2. Expand Management Node.
3. Right click on Database Mail and select Configure Database Mail and proceed with the wizard.
4. From Select Configuration Task screen, select Manage Profile Secrurity.
   
5. Now select your desired profile (mark CheckBox as Public) and make it Default Profile by select Yes.
   
6. Click Next and Finish the wizard.

Now you can send emails from SQL Server.

If you find this post helpful or have any suggestion, please post your valuable comments.