0

I have about 50+ reports being emailed to various users at various times throughout the day and night. However each morning when I check the emails that I should be receiving some of them are not emailed out. It's not the same reports that are the culprits everyday. I usually rerun the jobs in SQL however, today - no such luck. Does anyone have any ideas on what could be causing the reports not be emailed out. The lastStatus is the subscription table is "Done: 1 processed of 1 total; 1 errors." The log file says "The transport failed to connect to the server"

thanks for any suggestions

AdrianHHH
  • 13,492
  • 16
  • 50
  • 87
Sharlene Solomon
  • 1

1 Answers1

0

There is not a lot of detail to try and diagnose what the issue is, but these are some suggestions that may help.

  1. SSRS was not able to connect to the email server to send the email
  2. The database that the reports connects to is unavailable. maybe because of backups etc.
  3. Check in the various server logs (SQL, SSRS, SQLAgent, Exchange, etc) for clues as to why it went wrong.

EDIT
To View The data from the Execution logs on the ReportServer may give some clues. Works on SSRS 2008 R2

SELECT TOP 1000 *
  FROM [ReportServer].[dbo].[ExecutionLog3]
  Where RequestType = 'Subscription'
  And Status <> 'rsSuccess'
Nathan Fisher
  • 7,961
  • 3
  • 47
  • 68
  • Thanks for your response. I forgot to mention that other reports are being emailed. So sometimes report A won't be emailed but Report B will be (after the scheduled time for report A). Then the next day it could be report B that is not emailed. I do have a lot of reports being emailed. I'll check the SQL & SQL Agent logs now. SSRS logs only said the transport failed to connect to the server – Sharlene Solomon Sep 15 '14 at 06:13
  • Also looking to see if there is any sort of pattern to the errors may also help. When SSRS said that the `transport failed to connect`, I suspect that the SSRS server failed to connect to the exchange server. Only a guess though. If the reports run fine when run manually then it will probably be when trying to email the report. Also check that the email addresses are correct. We had an issue where usernames did not resolve to a valid email address when subscribing to reports. – Nathan Fisher Sep 15 '14 at 07:32
  • Thanks, The email addresses are correct and i have asked for the mailbox where the emails are sent from to be cleaned out (sent items was quite big). I'm struggling to see a pattern because this morning a different set of reports did not run. Reports that ran yesterday did not run today. Yesterday at 9am once of the scheduled reports ran correctly and then the other reports that were scheduled before 9am started coming through. – Sharlene Solomon Sep 16 '14 at 06:11
  • Ok, Another thing to look at is the Execution logs in ssrs – Nathan Fisher Sep 16 '14 at 07:44
  • Thanks very much. In the end bot SQL & the reporting services had to be restarted. That seemed to solve the problem – Sharlene Solomon Oct 13 '14 at 08:12