Skip to content

How To Configure Database Mail with GMail sp_send_dbmail

July 17, 2012

I know there are a ton of posts out there on this but for some reason it took me a few separate blog articles to get this working. My point here is to try and consolodate what I had to do in order to get my instance of SQL 2008 R2 working with GMail SMTP so I could use the sp_send_dbmail proc and hopefully accelerate your ability to use the feature. I needed this so I could work with a local install of SQL 2008 R2 and test out some mail functionality for a client. The email server at my work wouldn’t allow me to relay so it seemed gmail was my only option.

Here are two of the best blog posts I used to get me pointed in the right direction:

This one is from 2005, it’s useful but the settings I had to use were just slightly different and this article isn’t specific to configuring database mail. I’ll cover those changes later in this post.
http://lifehacker.com/111166/how-to-use-gmail-as-your-smtp-server

This post from Pinal Dave was very helpful as well. I just needed to get the two pieces working together.
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

One of the most important things from Pinal Dave’s blog is running the sp_confgure statements that enable database mail.
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

So after I merged those two posts together, I was close…so very close. A couple of things I still had to do:
1. When configuring the  GMail account, make sure the following settings are configured properly
    Reply e-mail: This will be set by gMail anyway so I don’ t think it makes a difference what you put here.
    Server Name: smtp.gmail.com
    Port Number: 587 (This is a change as port 25 would not work for me)
    This server requires a secure connection (SSL): I had to have this checked or it would not work. You cannot configure TLS but having this checked worked for me.
    Basic Authentication:
        User Name:
your gmail user name.  Mine did not work when I included the @gmail.com as mentioned in the previous post. 
        Password: Yep. Your gmail password. Nothing tricky here…
2. I had to make the profile I created the default profile. 
    To make the profile the default: Right Click “Database Mail” in Management Studio -> “Configure Database Mail” -> Click Next -> “Manage profile security” -> Click Next.
    Now you should see the profiles that have been created. I changed the only profile I had to “Yes” under “Default Profile” and checked the “Public” box. Click Next and then Finish. Oddly enough, if you supply @profile_name you shouldn’t need a default profile specified. I was providing that parameter and it was spelled correctly yet it gave me errors. According to BOL it was a total coincidence.

To execute it and see if it works you simply exec sp_send_dbmail which is found in the msdb database.

The parameters are well documented in BOL but here are a couple of quick tips to get you started:
To get the results of a query to show up in the body of your email you need to specify the @execute_query_database,  @query parameter and @attach_query_result_as_file = 0. To send it as a .txt attachment, just change the @attach_query_result_as_file = 1

You can also lookup the status of your emails by querying one of the sysmail_ tables/views in the msdb database. If you capture the output parameter @MailItemId from the sp_send_dbmail then that id can be passed to any of the objects to get the specifics on that particular mail message.  Note however that the “sent_status” doesn’t get updated real-time. It may show “failed” or “retrying” if it has problems initially but “sent” isn’t a given right away even though it may send fine. This is good to know if you try and raise a message indicating message failure/successs. In my scenario, I could only see “unsent” as a value right away but a few minutes later it would send. You can adjust your approach based on your needs but bear in mind the successful send isn’t available immediately after calling the stored procedure. Also, I’ve seen only those four values in the sent_status (“sent”, “unsent”,”failed”, “retrying”). There may be others…

Tables:
sysmail_attachments
sysmail_log
sysmail_mailitems

Views:
sysmail_allitems
sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems
sysmail_event_log

Configuration Tables:
sysmail_configuration
sysmail_account
sysmail_profile
sysmail_profileaccount
sysmail_principalprofile
sysmail_server
sysmail_servertype

I hope this helps you accelerate getting your database mail configured with gmail!

-Josh Thompson

Advertisements

From → SQL Server

2 Comments
  1. Aziz permalink

    Hello, I have tried everything on every blog I could find but for some reason my emails are not going out. It stays in sysmail_unsentitems table. Do I need to disable a port in order for emails to go out?

  2. Aziz, I’m not sure what to tell you. Have you looked at this article http://technet.microsoft.com/en-us/library/ms187817.aspx ? This has some good info on the use of that table. I’ll assume you followed the directions above, have a valid GMail account and are connected to the internet? I do not recall having to explicitly open any ports, though windows firewall may be causing you some of the issue; you could certainly look into that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: