June 12, 2012 at 10:35 am
I am using Sql Server 2005 Business Intelligence Studio to design reports and sql server management studio 2005 to write stored procedures that will generate the reports. I installed both of these tools onto my PC and they are both up and running.
I setup a table that contains the finished results and I go to print out those results in the Report.
When running this report, I pass in some parameters to a stored procedure which dumps the final records into the table that I use to run the report off of.
When I run this report the first time, by entering in the parameters and then clicking on the view report button, it does it thing and spits out the results. Then, when I go to select other types of parameters and run the report many times, that is when I am not getting any new results displayed in this report. I have to close out the report tool and then reopen it up to get another fresh run. I tried clicking on the refresh icon and pressing the F5 key, but not luck. Just an empty report.
Any possible suggestions on what is causing this problem and how I can fix it.
I have two datasets defined in this report.
1) The stored procedure that runs generates the report
2) A list from a table that is used in selecting one of the parameter values.
I notice that when I click on the data tab and run the query and then switch to the report tab and click the view report button then I get some different results to the screen. Do I need some type of timeout set to run the stored proc first or a refresh command I need to add to the stored proc.
June 13, 2012 at 1:08 am
SSRS uses caching to speed up report execution. My guess is that you're always working on the cache and thus seeing the same results.
There are some files you can delete to remove the cache, but my memory fails me here. A quick google search can probably help you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2012 at 1:43 am
Koen is right, in the folder where the solution is stored there are files with the extension rdl.data, these are the cached files, if you delete them it will recache the data on next execution.
There are a number of sites which say you can switch this off with a config setting in BIDS by hacking the XML config files but I have yet to get that working.
June 13, 2012 at 9:30 am
I will give it a try. Thanks.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply