SQLTeam.com | Weblogs | Forums

Reporting Services report not being received, Subscription Shows 1 Error

This is an existing RS report that has been running for months. What could cause a reporting services report to not get sent (or not be received), yet I sent a test email to myself and I received it? The RS job shows to be completing successfully. No errors in log for database mail.

(SQL Server 2019 Enterprise)
Thanks

@robert_volk as a RS expert, can you shed some light on my issue, please?

My data-driven subscription is failing. The Agent job succeeds. But in My Subscriptions for this report, it shows todays run had 1 error as shown below:

Done: 1 processed of 1 total; 1 errors.
This is where the RS logs reside on my installation:
C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles

ERROR: Error sending email. Exception: System.AggregateException: One or more errors occurred. ---> System.Net.Mail.SmtpException: Failure sending mail. ---> System.IO.IOException: Unable to read data from the transport connection: net_io_connectionclosed

No report was received by the recipients.
NOTE: I can run the report and download it, it's just the emailing phase that fails. This is a data-driven subscription.
Thanks very much.

You'll have to Google that IO exception error, I haven't seen it before. I found this thread:

https://social.msdn.microsoft.com/Forums/en-US/db6a2f36-9f7a-448d-89b8-968d45e13b45/systemnetmailsmtpexception-netioconnectionclosed?forum=netfxnetcom

It's quite old, and I can't say if any of the advice would help. Whenever I've had SMTP issues with SSRS it was either a firewall blocking the port, or the SMTP service was stopped or needed restarting for some reason.

If you're still blocked on this issue after 24-48 hours, I suggest you find a command-line or PowerShell email client that can send, test that it works in your environment, then reconfigure your subscription to generate the file to a shared folder of some kind. Then use your email sender to send that as an attachment. If that works, then it will isolate the problem to the SSRS email subsystem (I'm not 100% sure but I believe it's independent of SQL DB Mail), and it's a potential alternative method of emailing in case you can't figure out the problem.

If it doesn't work, then there is a downstream issue with your SMTP. I found another link that suggested it may be a TLS configuration problem. Again, my experience has been limited to basic SMTP problems external to the SQL Server/SSRS environment.

1 Like

Thanks very much, @robert_volk .All good tips. ,I'm asking my team if Windows updates were installed over the weekend. I always suspect that when things were fine on Friday but have issues on Monday :slight_smile:

Update. I created a new RS report and subscription. That subscription is not received either and the error log shows the same error. So it's a global issue on this server. I test DB Mail and it does work, I can send an email to myself. I have not
found a command-line or PowerShell email client that can send, test that it works in your environment.
Thanks

Powershell has a built-in cmdlet to send mail: Send-MailMessage

Open a PS command window on the server (RDP to the server) - execute Send-MailMessage passing the parameters. Ex: Send-MailMessage -SmtpServer smtp.domain.com -Subject "test" -To email@address.com

If that works - then something in your data-driven configuration isn't right. Validate each parameter is correctly formatted (e.g. multiple email addresses separate by a semi-colon).

What do you use as your smtp? have you worked out the details with those that manage this smtp server? sometimes some smtp servers have a throttling mechanism and depending on how often you are sending email, the smtp server might have put the sql server on risk list and is blocking things.

Who does it send emails as? it works for you but not for the sql job

Well, mysteriously, we received the RS report via email today. I know I didn't make any changes yesterday and others say the same. Very strange and troublesome because that means it can (and probably will) happen again. it had been running fine for 3 months before suddenly stopped receiving the daily report.

As I understand it, RS reports are sent via a different subsystem that regular DB Mail. DB Mail has worked all along on this RS server. We use Office 365 SMTP

not necessarily

you can configure it to use an smtp server

I have seen issues like this before - this is an SMTP issue where the email server is not working. Normally we don't see any errors, just not receiving the emails - and then they patch and restart the server and we start getting emails from months back (all queued up on the SMTP server).

1 Like

Makes sense. But in my case, we didn't receive any back-logged reports. Just the report for that day. It has now worked again for 3 consecutive days.