SharePoint SQL Reporting Services - OutOfMemory exception on large reports. How to solve?

Question

We have a bunch of reports on SharePoint, using SQL Reporting Services.

The statistics reports - the ones that aggregate data and display few hundreds to a few thousands records are loading fine.

However, we also have reports that display raw records from database. These reports usually have tens or hundreds of thousands of records. Sometimes even millions. And most of the times, they do not load but throw OutOfMemory errors.

The queries for those reports are very simple selects with some where conditions (sometimes, another few small tables might be joined on the huge one). In SQL Server Management Studio the query completes in 5-10 seconds.

I'm frustrated, because the clients are asking for the report, but I can't find any solutions to this (I googled a lot, but the best advice I could find was "get rid of the report or try to minimize the amount of data in it" which doesn't really solve anything - clients insist that they need the ENTIRE report.)

Is it possible to solve this somehow?

Answer 1

Move to 64 bit for the reporting server?

Chances are the users need the ENTIRE report because they are scraping the data off into excel or some other format and using it elsewhere. If you can get away with it, coding a webpage or similar that displays the query in a simple text format/csv may be more effective than a report.

I.e. The best advice you can find is the best advice.