SQLTeam.com | Weblogs | Forums

Mail queued but not delivered in sql server 2014


#1

I am trying to send the result of a SQL query through email using SQL Server 2014. The problem is that the e-mails are getting queued, but are not delivered to the recipient. There are some issues with the connectivity to the server. The description I am getting is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-04-05T16:05:09). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.130.109:25).
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MIS_Automation_Project',
@description = 'Mail account for office files.',
@email_address = 'my_email_address',
@display_name = 'MIS_Automation',
@mailserver_name = 'smtp.gmail.com' ;

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MIS_Automation',
@description = 'Profile used for mis automation project' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MIS_Automation',
@account_name = 'MIS_Automation_Project',
@sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MIS_Automation',
@principal_name = 'guest',
@is_default = 1 ;

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [clno] AS 'td','',[clname] AS 'td','',
[cladd] AS 'td'
FROM Client
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='

Client Information

'

SET @body = @body + @xml +'

Client No Client Name Client Address
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MIS_Automation', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'recipient', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;

How can I resolve this issue?


#2

Hi.
Not sure about SQL but in .Net if we need need to send trough gmail we need specify a port number (587 or 465)