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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy