Crystal Report hangs app if SQL returns too much data

[web.geek]

I am having a problem in of my company's applications (Visual Basic 6 SP5). The reports, using Crystal Reports Designer (Crystal Reports 8), will hang the application if too much data is sent from SQL Server. I use the general term "too much data," because it doesn't seem to occur consistently at a specific number of rows or bytes. One report dies after 1968 rows totaling approximately 276,000 bytes while the other dies at about 447 rows totaling approximately 1,040,256 bytes. I determined these limits by modifying the stored procedures to SELECT TOP X... until it failed.

Each report uses a stored procedure as its data source. The stored procedures each return a single recordset and execute fine within Query Analyzer. The only change to SQL Server from last year - when the program worked - is that we moved from a default instance of SQL Server 2000 SP3a to a named instance of SQL Server 2000 SP3a with a different port. Connectivity is not a problem, since the reports do work when the row count is less than or equal to the values stated above.

I have recreated the stored procedures. I have tested the program on another SQL Server that also uses a named instance with the same results. I have re-verified the database in each report and recompiled. I have exported the ".dsr" file as a Crystal report and successfully executed the report outside of VB.

Has anyone ever seen this behavior, or does anyone have any suggestions on where to begin to find a solution? I appreciate any help that I can get. Thank you...

[Malim]

I have experienced a similar problem. What error are you getting? I get a system. outofmemory.exception. We have done as much fine-tuning to the database and to the queries as possible, but there are over 24,000,000 header records and over 70,000,000 detail records in the database. Here's how I worked around it. Obviously, a report that can return that many records is not meaningful. So, I added parameters where the user could pull reports by customer, or location, or a date range, etc. It makes the report much more meaningful to the user. Maybe that could work for you?