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
| 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?