Last week I got some emails from Business Analyst’s and research teams that they didn’t receive the reports via email as usual. I checked the reportserver and found that they are correct. The jobs have failed. Now when I checked the subscriptions of the report I got the below error. It is little tough to check the subscriptions of a report in SQL Server 2005 and it irritates you as you have to either wait for the report to be executed and then click on subscriptions or cancel the execution and try you luck (most of the time it end up blocking) and start all over again.
From the above error from Report Manager site it is not clear as to what has happened. I tried to run the report manually and it worked fine completely. So I downloaded the report and forwarded it to all those recepients manually and then started troubleshooting. So now my clients are happy.
On troubleshooting, I found that it is not just this report but all the reports have not worked properly and failed with the same error. Now I checked the report server logs and found the below.
ReportingServicesService!emailextension!d!10/05/2011-13:25:08:: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The report server has encountered a configuration error. See the report server log files for more information. —> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!notification!d!10/05/2011-13:25:08:: Notification 2db5ae2c-2170-495e-b638-8bd6280d0a75 completed. Success: False, Status: Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information., DeliveryExtension: Report Server Email, Report: rpt0001 – Login Activity, Attempt 0
This makes it clear that it is not able to send the email.
Upon further looking I found that the login with which the reports were created is that of a DBA who left before I joined. I checked with the sysadmin team and they told they have removed his login from AD the last day. Now that almost ringed a bell. All I did to confirm was to create a simple report subscription with the DBA Account and it worked fine and I received the the report in email.
Now the challenge is to change owner of those subscriptions. I checked all over and couldnt find an option to change the owner and that is when I found this blog . All what my problem was , and how it occurred is made up as a story in Jeremiah Clark’s Blog and he has given the resolution for this. Yes edit the dbo.subscriptions table and change the ownerid.
After doing that the reports started working fine, all were happy and so was I 🙂
Happy Reporting !!