LessThanDot

If you want to mess around with Database Mail from your laptop and you don’t have a mail server, you can configure gmail for that purpose. It is pretty easy to setup, I will show you the screen from the wizard as well how to do it in T-SQL. I prefer T-SQL because I can run the same setup now on multiple machines

Below is the whole thing in one easy to run script

Make sure to change Your.Account@gmail.com to what your gmail account is, also notice that @enable_ssl =1 and that we are using port 587 (@port = ‘587’)

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = 'GmailDBMail') 
  BEGIN
    
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = 'GmailDBMail',
      @description  = '';
  END --IF EXISTS profile
  
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = 'GmailDBMail')
  BEGIN
    
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = 'GmailDBMail',
    @email_address           = 'Your.Account@gmail.com', -- <-- change this
    @display_name            = 'GmailDBMail',
    @replyto_address         = 'Your.Account@gmail.com', -- <-- change this
    @description             = '',
    @mailserver_name         = 'smtp.gmail.com',
    @mailserver_type         = 'SMTP',
    @port                    = '587',
    @username                = 'Your.Account@gmail.com', -- <-- change this
    @password                = 'yourpassword', -- <-- change this
    @use_default_credentials =  0 ,
    @enable_ssl              =  1 ;
  END --IF EXISTS  account
  
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = 'GmailDBMail'
                AND a.name = 'GmailDBMail') 
  BEGIN
    
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = 'GmailDBMail',
      @account_name = 'GmailDBMail',
      @sequence_number = 1 ;
  END

If you like to use the wizard, you can do that as well, here is what it would look like

Make sure to check SSL and that the port number is 587

Now that it is all done, let’s send a test email

You might have to restart SQL Agent for Database Mail to start working, so do that first.

Now execute the following stored proc, change your.account@gmail.com to where you want to send it

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'GmailDBMail',
    @recipients = 'your.account@gmail.com',
    @body = 'The test finished successfully.',
    @subject = 'Testing gmail with dbmail' ;

Go and check that email inbox, did you get the email? If you did not get the email, make sure to check the Database Mail Log, it will contain messages telling you what the error is. Right click on Database Mail and select View Database Mail Log

A message might be the following, this is because the port was wrong

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-01-13T08:04:38). Exception Message: Cannot send mails to mail server. (The operation has timed out.).

That is all there is to setting up Database mail with gmail

Setting up SQL Server Database Mail with gmail

About SQLDenis