Reporting Services Subscription fails but manually running report works

Question

Our company has a huge nasty report that takes about 50-60 minutes to run (it's for a university and lists all sorts of payment information for all students registered in courses). While it has been running each morning at 5am as a subscription, it recently stopped working and displays "An error has occurred during report processing." in the properties window for the subscription.

If I manually run the report from inside Visual Studio it will work every time, but the subscription will now always fail. I had our DBA turn on trace logging and it gave us no helpful information whatsoever. I've also set the subscription to run at different times throughout the day, with no success. The report is supposed to put an Excel file on a file share and it works for the other 5 subscriptions to this report (I have 6 subscriptions, only 1 of the 6 has a parameter set that returns values from a larger dataset). So this means that it has permission to write to the file share. Any ideas?

Answer 1

Could it be trying to write more than 65536 rows to the Excel file? If so it will just fail.

Also you might check the configuration for IIS to see if the report is causing a timeout.

Answer 2

wow, only 2000 row takes 1 hour. maybe ur hardware is sucks. or else, your query is nasty. u should perform most of sql operation in db, so that reporting services consume data from final (fact) table with simple statement like select from