February 17, 2009 at 9:54 am
Has anyone had problems with reporting services hanging when you add a new field to a stored procedure or add a new stored procedure to the report. Increasingly, I'm having this problem.
The stored procedure will run in 2 seconds or so, but when I try to run it under the data tab or run the report, even without modifying the report yet, it never completes. This will go on for hours or even a couple days of trying multiple times to run the report. Then suddenly the report finishes, and continues to finish in a reasonable time frame afterwards. This does not happen everytime and I have not been able to figure out any pattern yet.
We are working with SQL Server 2005.
Any help would be appreciated.
February 17, 2009 at 10:03 am
The SSRS designer is not intuitive I found when I alter a stored procedure it is easier and faster to take my existing report and create a new one with the altered store procedure.
The reason the datasource will bring back your data but your report will not use it.
Kind regards,
Gift Peddie
February 17, 2009 at 10:25 am
Thanks, I was actully thinking about that, except the same thing happens when I create a new report. I just created a new report, added the stored procedure as a dataset. Clicked run under the data tab, and it is hanging again.
February 17, 2009 at 10:30 am
That may be problem with your stored procedure because what I know it will run without issues but your report will not use the changes.
So go into SSMS and find out the issues with your stored procedures, I am assuming you know SSRS have issues with stored procedures using temp tables.
Kind regards,
Gift Peddie
February 17, 2009 at 10:39 am
What happens if you hit the refresh button?
The worst I saw is MS crash... but it never took hours (a couple more secs than it should have maybe, but nothing like you said).
February 17, 2009 at 10:54 am
I am not using any temp tables, but am using a common table. Will that cause similar problems.
Also, to the other response, are you refering to refresh on the data set in reporting services? If so, then yes and it still hangs.
February 17, 2009 at 11:00 am
Yes that's what I meant. CTEs never caused me that problem...
Don't know what else to tell you. Making a new report and copying everything is easy... but it's still a pain to do when you change the sps often.
Good luck. I'll let you know if I think of something else.
February 17, 2009 at 11:12 am
I also hit the field refresh button under the data tab and nothing happens.
I am relatively new to SQL Server. How do I check from management studio to see if there is a problem with my stored procedure? When I click on the run button under the data tab, it asks for the parameters, then the hour glass shows up, but nothing else happens. I once ran some sort of performance tool, and nothing showed up. It looked like management studio was not even trying to run the stored procedure.
February 17, 2009 at 11:24 am
How long does it take to run the sp in smss?
I doubt it takes it days to run...
February 17, 2009 at 11:37 am
This particular stored procedure takes 13 seconds. But I have had the same problem with one that only took 4 seconds to run.
February 17, 2009 at 11:47 am
That goes without saying... the problem is in VS.
Did you try shutting down VS before updating the definition in VS?
That seems to work for me once in a while. But that's almost as long as remaking the whole darn report.
February 17, 2009 at 11:49 am
You need to make sure your report parameters accept null or blank at the top of BIDs under Reports, then save all your reports and go into management studio and execute the stored procedure alone to see how long it takes to execute as stored procedure not as a datasource.
Kind regards,
Gift Peddie
February 17, 2009 at 11:54 am
I would try deleting the .data file and closing BIDS and SSMS and reopening and hitting refresh button on the data or preview tab.
February 17, 2009 at 12:14 pm
First, thanks for all the replies so far.
I have closed and reopened, removed and re added the stored procedure many times. I even shut down everything, left it over night and came back. So far this has had no effect. I have started adding a time out to the dataset so I don't have to keep hitting ctrl alt del everytime (and yes I still run it without the time out occasionally, so that is not the problem).
It seems like something gets stuck when a new stored procedure is created. Once it gets 'unstuck' everything works.
February 17, 2009 at 12:20 pm
I have written code that generates almost 200 pages by default in a test server so I cannot understand your reasoning here. You could also manually add the fields to the designer so you need to find out what is the issue.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply