![]() |
|
|
By: Mohammed Moinudheen | Read Comments (14) | Print Mohammed is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies. Related Tips: More |
|
One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc...
For this example, I have a SQL Server test environment configured and I want to test the alert mechanism using Hotmail. The following outlines the settings to do this.
If you are not familiar with setting up Database Mail you can refer to this link: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/. This will work for later versions of SQL Server as well.
When navigating through the database mail configuration wizard, you will encounter the below screen wherein the relevant details needs to be entered. Let's consider the example of using the SMTP server details for Hotmail. Enter the details as shown below.
Under 'Outgoing Mail Server (SMTP)'
Under 'SMTP Authentication'
The below table outlines the necessary SMTP server details and port numbers for Hotmail, Gmail, Yahoo and AOL (see this for more info http://support.microsoft.com/kb/2352963).
|
SMTP Server Details |
|---|
| Hotmail SMTP server name: smtp.live.com Port number: 587 |
| Gmail SMTP server name: smtp.gmail.com Port number: 587 |
| Yahoo SMTP server name: smtp.mail.yahoo.com Port number : 25 |
| AOL SMTP server name: smtp.aol.com Port number : 587 |
Here are some additional links you can refer to that show how to configure Microsoft Outlook to connect to Gmail, Hotmail etc... which is similar to what needs to be done for Database Mail.
Once your database mail profile has been configured you can send test emails for validation to verify setup is configured correctly. Right click on Database Mail and select Send Test E-Mail as shown below.

You could also execute the Database Mail system procedure sp_send_dbmail with the appropriate parameters using this sample script below.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Enter valid database profile name',
@recipients = 'Enter Valid Email Address',
@body = 'This is a test email sent from TEST server',
@subject = 'TEST EMAIL',
@importance ='HIGH'
After testing, if you are unable to receive notification emails, you could use the below Database Mail views for troubleshooting.
select * from msdb.dbo.sysmail_sentitems
This contains one row for each message successfully sent by Database Mail.
select * from msdb.dbo.sysmail_unsentitems
This contains one row for each message that has an unsent or retrying status.
select * from msdb.dbo.sysmail_faileditems
This contains one row for each Database Mail message that has a failed status.
Here are some additional views sysmail_event_log and sysmail_allitems. There is also a Database Mail system stored procedure msdb.dbo.sysmail_help_queue_sp which could be used as well.
For detailed steps on troubleshooting database mail issues, refer to this link: http://msdn.microsoft.com/en-us/library/ms188663.aspx
The above steps were performed using SQL Server 2008 R2 and I tested using Hotmail and Gmail. This should work for any version of SQL Server 2005 and greater.
Note: If your company has an SMTP server I strongly urge that you use your companies SMTP server and only use this where you don't have access to an SMTP server or if you need to test Database Mail.
| Share: | Share | Tweet |
|
![]() |
![]() |
Free SQL Server Learning |
| Friday, January 06, 2012 - 7:59:20 AM - rajendra |
|
|
Thanks for the Tip. |
|
| Friday, January 06, 2012 - 8:08:01 AM - Jason Yousef |
|
|
Nice one, Thanks! |
|
| Saturday, January 07, 2012 - 1:51:47 PM - Jason Yousef |
|
|
You need to pay to get a smtp access for yahoo. unless you use http://ypopsemail.com/.
|
|
| Monday, January 09, 2012 - 8:09:25 AM - Patel H |
|
|
I get same anti virus error mentined in my last comment even when trying with hotmail.. |
|
| Monday, January 09, 2012 - 8:13:34 AM - Mohammed Moinudheen |
|
|
@Patel: Just to remind, I would not recommend to change anything on your company laptop. |
|
| Monday, January 09, 2012 - 11:18:28 AM - Ankit Shah |
|
|
nice article which i was looking for to setup on my local machine and you comes out with this. Great. thanks |
|
| Monday, January 09, 2012 - 11:53:29 AM - Patel H |
|
|
Its McFee - I know I wouldnt remove antivirus on my work machine....I think I have answered my own question...but thanks.. |
|
| Wednesday, January 11, 2012 - 6:56:35 AM - bojanna mk |
|
|
Good article Moinu.Really Intersting! |
|
| Wednesday, January 11, 2012 - 7:27:58 AM - Srinath |
|
|
Thank you for such a Very nice article...Keep going Moinu...!! |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |