SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failure sending mail: Failure sending mail.


Failure sending mail: Failure sending mail.

Author
Message
dweil
dweil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1542 Visits: 3467
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.




Thom A
Thom A
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53750 Visits: 16682
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41851 Visits: 11549
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
dweil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1542 Visits: 3467
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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41851 Visits: 11549
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
dweil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1542 Visits: 3467
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
Jeff Moden
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: General Forum Members
Points: 620483 Visits: 45224
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dweil
dweil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1542 Visits: 3467

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'






Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search