Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

BIDS 2008 R2 crashes when previewing large report

I have a report that is pretty straightforward.  I have one tablix that has about 1100 rows and about 150 columns.  There's no calculation that the report has to do, everything is already pre-aggregated in the query.  When I change my query to do a select top (1000), it works just fine, but when I change it to select everything, the application crashes with an "unexpected" status.  There aren't any other explanatory errors.  I have not tried deploying the report and seeing if it works on my reporting server.  Any help in figuring out why I'm getting this behavior would be appreciated.
0
kenyayvette
Asked:
kenyayvette
  • 4
  • 4
1 Solution
 
lcohanDatabase AnalystCommented:
Please try to execute the same query in SSMS against your actual SQL database and see how long it takes to run and how much data needs to be returned as this may be the actual hidden reason for your "Unspecified error..."
0
 
kenyayvetteAuthor Commented:
The data returns in SMS in seconds and returns about 160,000 rows.  I have other reports that I have built that run successfully off of the same database, so it shouldn't be a permissions issue either.  
0
 
lcohanDatabase AnalystCommented:
" I have other reports that I have built that run successfully off of the same database " - but do they return 160,000 rows? I doubt that because it looks pretty high number off records to be HTML formated and I'm pretty sure that's your problem as you can successfully run it in BI for 1000 rows but not for the 160,000 rows.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kenyayvetteAuthor Commented:
This report does not return 160,000 rows in reporting services either.  The report is basically doing a pivot on that data, so as I stated in my initial post, it has about 1100 rows and about 150 columns.  Either way, surely that's not too much data for reporting services to handle.  
0
 
lcohanDatabase AnalystCommented:
Ok so the report tablix with about 1100rows and 150 columns sits on the top of this query that returns about 160000 rows correct? If yes, did you tried to put that query in a SQL view instead of running it in reporting server through BI design?
0
 
kenyayvetteAuthor Commented:
Do you mean did I try to pivot the data in sql as opposed to in SSRS?  No, I did not try to do that.  I only put the base select statement in SQL.  So I my statement just looks like this:  select * from tblmemberdetail where companyname = 'CompanyA'.  

I actually just tried deploying the report to the server and running it from there, and it actually returned with no problems.  Is there some type of memory limit in Visual Studio when you're using the preview pane?
0
 
lcohanDatabase AnalystCommented:
Even if you use shared datasets cached in SSRS I would still move the query that returns 160000 rows in a SQL View and run the Select in SSRS based on that SQL view.
As far as  VS and Windows memory....well I have issues opening 10 000lines through VS2005/2008 - BIDS or non BIDS projects on server with 64 GB of ram so try figure...You could try run VMMap to get a more detailed look at Visual Studio‚Äôs virtual memory usage in your specific environment.
VMMap:
http://www.voyce.com/index.php/2009/07/28/diagnosing-out-of-memory-errors-with-vmmap/

memory limitation:
http://www.pcreview.co.uk/forums/memory-limit-visual-studio-2005-a-t2904648.html
http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.languages.csharp/2006-12/msg04057.html
0
 
kenyayvetteAuthor Commented:
Sorry it took me so long to get back to this question.  I basically have to develop the report using a smaller subset of data in BIDS, but everything runs fine and in good time on the server itself.  Thanks for your help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now