Both servers are able to normally send email without issue
server a is able to send the email for the query below, but server b has an error message saying, "mail could not be sent to the recipients because of the mail server failure. Exception message: Cannot send mails to mail server."
Test emails and other jobs with sp_send_dbmail steps work fine on both servers.
Doesn't matter if done as job or in a query window behavior on both servers is consistent.
Failing server is at 12.0.5203.0, working box is 12.0.5511.
Code is:
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
select cast(getdate() as date)
SET @subject = 'Detail' + cast(cast(getdate() as date) as varchar)
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">Header</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green">
<th>operation_id</th>
<th>event_message_id</th>
<th>Date</th>
<th>Time</th>
<th>Message</th>
<th>EventName</th>
<th>message_source_name</th>
</tr>' +
CAST ( (
SELECT
td = operation_id,'',
td = event_message_id,'',
td = cast(message_time as date) ,'',
td = cast(message_time as time) ,'',
td = MESSAGE ,'',
td = event_name ,'',
td = message_source_name
FROM (
SELECT em.*
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT max(execution_id) FROM SSISDB.catalog.executions where package_name='packagename.dtsx')
AND event_name NOT LIKE '%Validate%'
)q
ORDER BY message_time
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients='someone@somewhere.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;
Body is inline so CU4 isn't the issue. Any thoughts or suggestions would be appreciated.
@profile_name = <email_profile>parameter with thesp_send_dbmailSP. Otherwise check the email server you are using to send the SMTP from and see if the IP address of the server has some sort of unauthenticated relay type access. If you can control and change the server IP addresses and test, go for it simple enough; otherwise look over the Exchange / email server logs for more verbose detail of anything at the email server level. So the DB mail profile, email server relay settings, & verbose logs of the email server—starting points. – IT Thug Ninja Oct 05 '17 at 23:27