Tuesday, September 23, 2014

0 Configure SQL Server Database mail with Gmail

Database is another executable DatanaseMail.exe  that invoked by SQL server. 

image

This is the quick work through for how to enable SQL server database mail with Gmail .

First, we need to enable the database mail in the SQL server.

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';
go
 
select name,value,value_in_use,description from sys.configurations where name='Database Mail XPs'
image
sp_configure 'Database Mail XPs',1
go
reconfigure with override
go

image

We now need to create the profile.

image

image

This link https://support.google.com/a/answer/176600?hl=en outline how to setup the smtp server for gmail. Base on my testing, we have to use port 587 and user name must be the full email address ( including gmail.com)

image

Above steps can be replaced with the SQL below

EXECUTE msdb.dbo.sysmail_add_account_sp
   @account_name = 'SGC4_MAIL',
   @email_address = 'XXXXXXXX@gmail.com',
   @display_name = 'SGC3 SQL Server',
   @mailserver_name = 'smtp.gmail.com', 
   @mailserver_type='smtp',
   @port=587,
   @username='XXXXX@gmail.com',
   @password='XXXXXXXXx',
   @enable_ssl=1;

Query msdb.dbo.sysmail_account to list all the current email accounts.

select * from msdb.dbo.sysmail_account;

image

image

image

image

Above Steps can be created via SQL as below

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SGC4_default',
    @description = 'Profile used for administrative mail.' ;
 
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SGC4_default',
    @account_name = 'SGC4_MAIL',
    @sequence_number =1 ;

After profile is created, we can check msdb.dbo.sysmail_profile

select * from msdb.dbo.sysmail_profile

image

We can send the test email .

image

image

Alternatfully , we can use the sp_send_mail to test as well.

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='TestMail',
@recipients = 'po@sqlpanda.com',
@body='This is test from sp_send_dbmail',
@subject='This is test from sp_send_dbmail'

Others

--- Chek the mail Q
EXEC msdb.dbo.sysmail_help_queue_sp
EXEC msdb.dbo.sysmail_stop_sp;
--- start the database process
EXEC msdb.dbo.sysmail_start_sp;
---Delete msdb.dbo.symail_allitems
EXEC msdb.dbo.sysmail_delete_mailitems_sp
-- Delete msdb.dbo.sysmail_event_log
EXEC msdb.dbo.sysmail_delete_log_sp

Tables

select * from msdb.dbo.sysmail_log
select mailitem_id,recipients,body,sent_status,sent_date from msdb.dbo.sysmail_allitems
  • sysmail_log : list the high level of the database mail event.
  • sysmail_allitems: store each eamil’s detail
  • sysmail_configuration: store configuration for the mail

Reference

http://craftydba.com/?p=1025

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates