August 10, 2018 at 12:35 pm
Hi friends,
I have been using the google smtp server and the live mail smtp server to try to send email from my SQL Server Database. I found some instructions on how I can perform this in the following link https://www.c-sharpcorner.com/article/configure-database-mail-send-email-from-sql-server-database/. It seems very straightforward, however, I have been unable to get this functionality to work. Are there any trouble shooting tips that someone could please give me so that I can get this feature to work. Thanks !
August 10, 2018 at 12:49 pm
make sure port 25 is open between smtp server and sql server. , you may use following script to setup mail (change values as required)
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go
--------------------------------------------------------------------------------------------------
-- BEGIN Mail Settings Default
--------------------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'Default')
BEGIN
--CREATE Profile [Default]
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Default',
@description = 'Default profile';
END --IF EXISTS profile IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'account_name')
BEGIN
--CREATE Account [account_name]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'account_name', -- replace with any name you want, tipicially hostname
@email_address = 'DoNotReply@someone.ca', -- sender email address , can be dummy email address
@display_name = 'display_name', -- replace with sender display name
@replyto_address = '', -- replace with reply to address or leave empty
@description = 'Default account',
@mailserver_name = 'your.mail.smtp.server.address.or.ip', -- replace with smtp address or smtp ip
@mailserver_type = 'SMTP',
@port = '25',
@username = NULL ,
@password = NULL ,
@use_default_credentials = 0 ,
@enable_ssl = 0 ;
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 = 'Default'
AND a.name = 'account_name')
BEGIN
-- Associate Account [account_name] to Profile [Default]
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Default',
@account_name = 'account_name',
@sequence_number = 1 ;
END
August 10, 2018 at 12:58 pm
gmail has to use port 587 as well as the use_ssl flag.
here's the exact script i use. replace"sqlnotifications@gmail.com" with your gmail address instead.--#################################################################################################
-- BEGIN Mail Settings Gmail
--#################################################################################################
DECLARE @decr varchar(128) ;SET @decr = 'NotTheRealPa$$w0rd'
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'Gmail')
BEGIN
--CREATE Profile [Gmail]
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Gmail',
@description = 'gmail setup';
END --IF EXISTS profile
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'sqlnotifications@gmail.com')
BEGIN
--CREATE Account [sqlnotifications@gmail.com]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'sqlnotifications@gmail.com',
@email_address = 'sqlnotifications@gmail.com',
@display_name = 'SQL Notifications',
@replyto_address = 'sqlnotifications@gmail.com',
@description = 'Google gmail account',
@mailserver_name = 'smtp.gmail.com',
@mailserver_type = 'SMTP',
@port = '587',
@username = 'sqlnotifications@gmail.com',
@password = @decr,
@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 = 'Gmail'
AND a.name = 'sqlnotifications@gmail.com')
BEGIN
-- Associate Account [sqlnotifications@gmail.com] to Profile [Gmail]
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Gmail',
@account_name = 'sqlnotifications@gmail.com',
@sequence_number = 1 ;
END --IF EXISTS associate accounts to profiles
Lowell
August 10, 2018 at 12:59 pm
Hi Ronnie,
When using Gmail, you have to enable POP and IMAP and port number should be 587 for smtp.gmail.com. Refer to this article for detailed instructions
https://mssqltrek.com/2011/08/16/configuring-sql-server-db-mail-to-send-emails-to-your-gmailyahoo-mail/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply