2

I have a report which is pulling data from a stored procedure however only the headers appear when I run the report without any data. I can track the execution of the procedure in SQL Profiler and when I copy the executing statement with it's parameters into SQL Management Studio the statement runs and returns several thousand rows of data from the same DB.

The data set timeout is set as 0 and no timeout error is given so I don't think it's related to the quantity of data returned however in management studio it does take 10 minutes to run the command.

How do I fix this issue?

UPDATE: The issue seems to be linked to the fact the report is gathering data from a dataset which is using a stored proc 'query type' as when I enter an exec statement as a text query type it does return some data but then I can't change the parameter when running the report.

Inverted Llama
  • 1,522
  • 3
  • 14
  • 25
  • You aren't by chance [using Chrome to view the report](http://stackoverflow.com/questions/6385710/visualstudio-report-viewer-not-rendering-report-body-in-chrome)? – StuartLC Jan 22 '13 at 12:06
  • @StuartLC Nope, using IE 8 and 'run' in SQL Server Report Builder itself – Inverted Llama Jan 22 '13 at 12:10
  • Add an image of how your report looks like. – eestein Jan 22 '13 at 12:12
  • Perhaps [this answer](http://stackoverflow.com/questions/10996747/ms-reporting-services-limiting-number-of-rows/11057749#11057749) can help you track down the problem? – Jeroen Jan 22 '13 at 12:59

2 Answers2

1

A couple of things to try:

  1. Test the pocedure outside of reporting services to see if the results come out. If not see if the procedure is buggy.

  2. The results from the procedure, for memory, need to be in the form of a ref_cursor, a table of object or array type.

  3. The 10 minutes to run could be an issue. I suggest trimming down the procedure to run quicker, even if you cut a lot out of it just to test if it works with a quicker response. An example could be to use the same return type but returning a dummy table.

glh
  • 4,900
  • 3
  • 23
  • 40
1

Not sure why this fixed it but I played about the the bindings between the .RDL for a while and it seems to have fixed the issue. In report builder it looks exactly the same but according to SVN I've pretty much re-written the whole thing.

Inverted Llama
  • 1,522
  • 3
  • 14
  • 25