Failure sending mail: Failure sending mail.

Question

Failure sending mail: Failure sending mail.

dweil

SSCrazy

Points: 2408
More actions
April 12, 2018 at 9:20 am

#330380

I'm running into a strange problem.
My ssrs subscription are working except for one report which was previously working.
The report is automatically email to a group of users on a schedule.
It continues to go out on schedule but all formatting is lost. It is more like a list of what is on the report.
Interestingly on the the my subscriptions it shows "Failure sending mail: Failure sending mail".
The report works fine when I run it manually with the same parameters.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Have a look at the logs, they should give more detail on what the issue is. If you're unsure, please post the relevant parts.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sue_H SSC Guru Points: 90860 More actions · Answer 2

dweil - Thursday, April 12, 2018 9:20 AM
I'm running into a strange problem.
My ssrs subscription are working except for one report which was previously working.
The report is automatically email to a group of users on a schedule.
It continues to go out on schedule but all formatting is lost. It is more like a list of what is on the report.
Interestingly on the the my subscriptions it shows "Failure sending mail: Failure sending mail".
The report works fine when I run it manually with the same parameters.

Check the Reporting Services log for additional information around the time that the subscription would have run. The logs are located at:
Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles
It sounds like you should likely find some more information in the logs.

First thing I thought though when you said it sued to work is that odd things happen with subscriptions if the owner is no longer an SSRS user. The owner id in the subscriptions table maps back to the user id in the users table. I'd check that just to make sure ownership of the subscription isn't an issue.

Sue

dweil SSCrazy Points: 2408 More actions · Answer 3

I checked the log file and found the message below.
The same user (myself) has other subscriptions that work and email without a problem.
The report is actually being emailed without formatting.
I can't see what the error below has to do with this problem.

emailextension!WindowsService_0!25a4!04/12/2018-11:37:21:: e ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. ---> System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.QuotedPrintableStream.FlushInternal()
at System.Net.Mime.QuotedPrintableStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.MimePart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mail.SmtpClient.Send(MailMessage message)

Sue_H SSC Guru Points: 90860 More actions · Answer 4

dweil - Thursday, April 12, 2018 11:43 AM
I checked the log file and found the message below.
The same user (myself) has other subscriptions that work and email without a problem.
The report is actually being emailed without formatting.
I can't see what the error below has to do with this problem.
emailextension!WindowsService_0!25a4!04/12/2018-11:37:21:: e ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. ---> System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.QuotedPrintableStream.FlushInternal()
at System.Net.Mime.QuotedPrintableStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.MimePart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mail.SmtpClient.Send(MailMessage message)

It couldn't keep writing everything necessary to the smtp server. Those errors are usually (not always) more on the smtp side. Have whoever manages the email/smtp side of things check the smtp logs for errors. It could be different things. One of the configuration issues is that it could possibly be exceeding size limitations or limitations with the account. Those should show up as errors on the smtp side. When you send one manually, that's just one. The subscription sends more than one.
A high volume of activity at the time could also contribute so you may want to see if you have certain times a lot of subscriptions are sent out and stagger the times a bit if your able to do so.
But you would want to check the smtp side for errors or any message/account limitations.

Sue

dweil SSCrazy Points: 2408 More actions · Answer 5

I'm still waiting on our email administrator.
However, I did find the problem and the fix.
Not that the error messages gave any indication as to what the problem was.
I tried subscribing to the report with different parameters and time periods.
Mostly it worked but not always.

The issue was with the data.
One of the varchar fields that was coming from sqlserver has a "line feed period linefeed" in it. ( hex 0A 2E OA).
The abnormal code only caused our exchange server grief but was not visible when manually bringing up the report.
I wrote some code in the stored procedure to strip out carriage returns and line feeds from those fields.
I'm guessing one of our users copied some text from a web screen to the application that uses that database.

Thanks for all your help

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

dweil - Friday, April 13, 2018 12:16 PM
I'm still waiting on our email administrator.
However, I did find the problem and the fix.
Not that the error messages gave any indication as to what the problem was.
I tried subscribing to the report with different parameters and time periods.
Mostly it worked but not always.
The issue was with the data.
One of the varchar fields that was coming from sqlserver has a "line feed period linefeed" in it. ( hex 0A 2E OA).
The abnormal code only caused our exchange server grief but was not visible when manually bringing up the report.
I wrote some code in the stored procedure to strip out carriage returns and line feeds from those fields.
I'm guessing one of our users copied some text from a web screen to the application that uses that database.
Thanks for all your help

Can you post the "stipping out" code that you settled on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dweil SSCrazy Points: 2408 More actions · Answer 7

I found the exact syntax in a another unrelated sqlservercentral post once I figured out what the problem was.

replace(replace(table1.col1, char(13), ''), char(10), '') AS 'updated_field'