I had an issue trying to find what was causing a data-driven report subscription error. The SSRS report is deployed to SharePoint, and Reporting Services is in SharePoint Integrated mode. The subscription was showing last results as “Done: 15 processed of 15 total; 7 errors.” It took awhile to find the pieces I needed to figure out what was causing the error.
I found some information about looking at Report History in SharePoint and creating a New Snapshot, which would give me the last known error. However, since some of the reports had run successfully this did not work.
Next I tried looking at the Report Server database in the ExecutionLog tables. I isolated the query results to just the one report subscription by writing a query like this:
select * from ExecutionLog3
where RequestType = ‘Subscription’ AND timeStart >’2012-05-29 11:30:00.000′
ORDER BY TimeStart DESC
This only returned the successes, not the failures.
Finally, I tried looking in the Report Server Trace Log file. There was very little in the log file and nothing to do with my subscription. I knew that the Trace Log file should hold the information I needed. After much poking around I realized that I had made a fundamental error in my assumption about the architecture. The Trace Log file resides on the SharePoint server, not the Report Services database server.
Find the Trace Log
The Trace log files can be found on the SharePoint server, usually here: C:\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles
Find the ScheduleID for your Subscription
Find the Schedule ID of the most recent subscription by querying the ReportServer database:
‘SubnDesc’ = s.Description,
‘SubnOwner’ = us.UserName,
‘LastStatus’ = s.LastStatus,
‘LastRun’ = s.LastRunTime,
‘ReportPath’ = c.Path,
‘ReportModifiedBy’ = uc.UserName,
‘ScheduleId’ = rs.ScheduleId,
‘SubscriptionId’ = s.SubscriptionID
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId
Search for the ScheduleID in your Trace Log and find the Error message
Open the appropriate log file based on the time stamp being the most recent after the subscription ran, and search for the ScheduleID. Once you find the first entry for your ScheduleID, look for anything that starts with e ERROR
The error message can look like this:
library!WindowsService_113!308!05/29/2012-10:31:17:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter ‘Manager’ is not a valid value.;
In preparing this post, I found the following articles to be useful: