Only 2 reports out of >10 fail to render on SSRS 2008R2 SP2

Question

The whole set of reports was working fine in SSRS 2005 production and is still working fine in SSRS 2008 R2 SP2 Dev/UAT area, but on the new production server two of those reports fail immediately (not a timeout issue!) with the following errors:

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) 

There do not seem to be any differences between those 2 reports and the rest in the way they access database (using a single shared datasource) or query data (embedded SQL). Their SQL statements work fine in design/Run Query mode and return just as instantly as in the rest of reports. The reports also work fine in both BIDS on a dev machine and ReportBuilder in the prod machine.

There was the same but intermittent issue with another report for a couple of times right after we uploaded all of them, but I cannot reproduce it anymore for a week now.

Since then I re-created a brand new SSRS DB, datasource, downloaded the report from UAT where it is working and uploaded to the new server where it keeps failing before even displaying the parameter panel.

Tried the following without success:

• Increased SSRS database growth size from default 1 to 10 MB (there is plenty of space for data and logs)
• Increased 'max text repl size' parameter of the server to 2147483647 or to unlimited (-1)
• Added the user to RSExecRole (works without that in UAT)
• Changed from shared datasource to embedded and back

By turning on SSRS verbose debugging I collected the logs and found that when report is invoked, it successfully executes one dataset of two and then fails with the error above without writing any more entries into the log. From the verbose log it does not even seem to be trying to execute the 2nd dataset.

Can there be any other reasons for this type of failure other than timeout/lack of space?

Answer 1

After a lot of stairing at the 2 reports in question and the rest and seeing how they are very similar with only actual report datasource differences, I finally was approved for extra time to re-create them from scratch.

I started by only creating the parameters and dumping their values on the report. Then I added one of the datasets. The test report still worked. One of the parameters was originally query-based multi-select and hidden. It was used to look up the translation values English to French using the parameter values array. This was obviously a hack, but heck, it worked for this dozen of reports for over 10 years. Once I configured the parameter to use the dataset for the available values, the error above was reproduced.

As I would have never designed the lookup that way, I removed the query-based hidden parameter altogether and changed all cells using it for lookup to directly use Lookup() function on the underlying dataset.

The reports work now. We did not touch the rest of the reports and they are still using that "hack". Go figure what SSRS did not like about these two.