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">
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; } 
N'<H3><font color="Red">Header</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green">
</tr>' +
CAST ( ( 

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%'
ORDER BY message_time 

EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;

Body is inline so CU4 isn't the issue. Any thoughts or suggestions would be appreciated.

  • 1
    With that error message, I doubt the issue is in the stored proc code. It sounds like something else, potentially outside of SQL. Try working through some of these queries first. Oct 5, 2017 at 20:53
  • Are there differences in the size of the body between the two servers? Could server B be crossing a limitation on the acceptable size of an email message, while server A is still under that limit?
    – RDFozz
    Oct 5, 2017 at 20:59
  • Update to CU4, why not? Also, the error message states the email server has a problem. Look there.
    – Hannah Vernon
    Oct 5, 2017 at 21:24
  • Maybe you're missing a DB mail profile specified as the @profile_name = <email_profile> parameter with the sp_send_dbmail SP. 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. Oct 5, 2017 at 23:27
  • I have an answer here with some similar stuff you are doing in case you find it helpful: dba.stackexchange.com/questions/139187/… I don't build the HTML tables like that though since the other way always works great for all uses and queries for the most part and I just change accordingly. Oct 5, 2017 at 23:31


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.