Send Email From SQL Server Database

• Data Rat

  SSC Eights!

  Points: 943

  More actions

  August 10, 2018 at 12:35 pm

  #415030

  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 !

• goher2000

  SSCertifiable

  Points: 5479

  More actions

  August 10, 2018 at 12:49 pm

  #2001298

  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

• Lowell

  SSC Guru

  Points: 323524

  More actions

  August 10, 2018 at 12:58 pm

  #2001299

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  August 10, 2018 at 12:59 pm

  #2001300

  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/