Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Setting up Database Mail for SQL Server


By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | More > Database Mail

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

Many things have changed starting with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail.  This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work.  With SQL Server 2005 and later this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain.  So how do you setup Database Mail for SQL Server 2005 and later?

Solution

There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server or by using SQL Server Management Studio.  For this exercise we will walk through how to setup Database Mail by using the GUI.

To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on "Database Mail".

configure database mail from ssms

Then select "Configure Database Mail' and you will get the following welcome screen and the click "Next".

database mail wizard

The following screen will appear and select "Set up Database Mail by performing..." and click "Next".

database mail select configuration task

If Database Mail has not been enabled, you will get this following screen. Just click "Yes" to enable it.  If it has already been enabled this screen will not appear.

enable database mail features

Enter in a name for the Profile and also a description and click "Add..."

database mail new profile

The following screen will appear.  Fill out the details for your mail account that will be used to send out email from SQL Server.  When you are done click "OK".

database mail new database mail account

After you click "OK" you will be brought back to this screen and the SMTP details will now show for the account you just setup.  Click "Next" to continue.

database mail new profile

On the next screen you will see the name of the profile that you just setup.  Click on the checkbox to allow this to be a Public profile and also select "Yes" for the default profile and then click "Next".

database mail manage profile security

The following screen has some additional parameters that can be set to control how mail is sent.  You can make changes or leave the defaults.  When you are done click "Next".

database mail configure system parameters

A summary screen will appear that shows you all of the options that were selected.  If everything is correct click "Finish" or click "Back" to go back and make changes.

database mail complete the wizard

When you click "'Finish" the next screen will appear that shows you the status of installing Database Mail. When this has finished just click "Close" to close this screen.

database mail configuration success

To test Database Mail, right click on Database Mail and select "Send Test E-Mail".

database mail send test email

Fill in a "To:" email address and change the body of the email if you want and then click "Send Test E-Mail".

database mail send test email

After you have sent the email you will get this message box to confirm if the email was received or not.  If it was you can click "OK" to close the screen or click "Troubleshoot" which will launch the help information to see what the issue may be and how it can be resolved.

database mail check test email send

That's all there is to it.  As I mentioned before this can also be setup by using stored procedures. To look at this approach take a look at this article Database Mail in SQL Server.

Next Steps
  • Setting up Database Mail is not that complicated and it is much easier then SQL Mail. Take the time to see how this new approach to mail can work in your environment
  • After you setup Database Mail don't forget to setup your operators, alerts and SQL Agent alert settings


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 31, 2017 - 12:25:21 PM - Greg Robidoux Back To Top

What version and edition of SQL Server are you using?


Thursday, August 31, 2017 - 10:38:07 AM - Haim Rubin Back To Top

Hi

Is the mailer is not installed on the machine. How do I add it

Thanks

 

 


Wednesday, June 24, 2015 - 12:33:52 PM - margarett hance Back To Top

Great TIp - easy to follow  Database EMAIL


Friday, January 30, 2015 - 11:32:59 AM - Andy Bartholomew Back To Top

This was great, thanks!


Friday, April 12, 2013 - 7:02:24 AM - Dinesh Vishe Back To Top

hi,

 

select 

 

*from msdb.dbo.sysmail_profile => Profile creation details

 

select 

 

*from msdb.dbo.

sysmail_faileditems=> failed mail

 

 

select

 

 

*from msdb.dbo.sysmail_profileaccount => Profile Modify user

select

 

 

*from msdb.dbo.

 

sysmail_sentitems => send mai details

 

 

select

 

*from msdb.dbo.

sysmail_event_log => All event details.

 


Tuesday, April 02, 2013 - 5:57:31 AM - suresh y Back To Top

HI GREG,

HOW R U 

I AM SURESH.  WE ARE GIVING BEAUTIFUL TIPS FROM U R MSSQLTIPS. I AM FOLLOWING DAILY  YOUR BLOG PLEASE SOME MORE

SUGGESTIONS.  I AM DOUBTFUL "SQL MEMORY ARCHITECTURE'' I AM ASKED SO MANY AUTHORS BUT I AM NOT GETTING FULL FLDGE

KNOWLDGE ABT THAT TOPIC. LOT OF CONFUSION 32 BIT ARCHITECTURE AND 64 BIT ARCHITECTUR, AWE TOPIC ,BUFFERPOOL AND

DATA CACHE,CHECHPOINT,DIRTY PAGES

 


Friday, November 09, 2012 - 1:03:12 PM - Jacz Down Back To Top

Where accept any certificate as they use a self-signed?

Sorry for my English..

 

 


Friday, October 26, 2012 - 12:37:39 PM - anwar ahmed Back To Top

Great help


Tuesday, August 21, 2012 - 1:25:46 PM - sai Back To Top

*Very good article


Monday, July 02, 2012 - 3:43:28 AM - john the rule Back To Top

Very good guidelines with  wizard


Learn more about SQL Server tools