SSRS report built in VS 2012 runs, but deployed to SSRS 2008 server times out

0
votes

WE have a large collection of reports that run on our 2008 server correctly. They've all been developed in Visual Studio 2012.

However, one report makes the SSRS service run at 100% CPU usage on the server, requiring a restart of the service.

This is all happening on the Dev server right now. We have SSRS and our SQL database on the same server.

The report takes 10 parameters and for our test case, returns 7 rows with 39 columns. Running the query with the same parameters in Sql Management Studio returns the 7 rows in less than a second, so it seems to be rendering the report that kills the server.

I can look at the SSRS database tables, but the logs there haven't told me much. What should I be looking for? What can I check? And what information can I provide to the community to get more help?

1
visual-studio-2012reporting-servicesssrs-2008
Have you set the Target Server Version on your Report Project Properties to SQL Server 2008? – Ron Smith
Check out my answer about parameter sniffing in case that is the issue – Chris Latta
TargetServerVersion is set to 'SQL 2008 R2 or later', and the server is R2. I may try just SQL 2008. Good thought. – M Kenyon II
Chris Latta, I added the internal parameters to the stored proc, still runs overtime. However, something to note. This stored proc was written to be dynamically grouped. We pass in a varchar field, do the initial select on the parameters into a #tempTable, then write dynamic SQL to group based on the GroupBy parameter. Better alternatives? – M Kenyon II
I removed the Dynamic SQL from the stored proc to test, and the report still runs for over 15 minutes. That's with the internal variables still in place. – M Kenyon II

1 Answers

0
votes

I recreated the report from scratch, same stored proc, and it works as desired. Something in the report definition must have gotten corrupted some how.

Sorry I couldn't find out specifically what was wrong.