2

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.

  • 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. – Erik Darling Oct 5 '17 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 '17 at 20:59
  • Update to CU4, why not? Also, the error message states the email server has a problem. Look there. – Max Vernon Oct 5 '17 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. – Pimp Juice IT Oct 5 '17 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. – Pimp Juice IT Oct 5 '17 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.