DataTrim (7/17/2014)
HiI have a parametised SSRS report that when is first run it returns incorrect results (i.e. number of rows and grand total of a value column aren't correct). When run for a second time, and every time after, the correct results are returned until a parameter is changed where a similar thing happens again (but not always). The report is running against static tables in a data warehouse. Both the warehouse and SSRS report are SQL 2005.
The report uses a stored proc. I have run the SQL from the stored proc using the same parameters that generate the problem in the SSRS report and get the same results - first run incorrect number of rows returned then correct on second, third etc execution. This seems to go against everything I know of databases in that the same result should be returned every time.
Does anyone have any ideas on what could be causing this behaviour as all my investigations and tests so far have drawn a blank.
Thank you.
I have actually seen this. I would guess your tables have some Indexes, and transaction logs are enabled and being backed up for increments longer than an hour, you have set no level of transaction isolation or locking hints in the Queries to your DataWarehouse tables.
I set the Recovery Option to Simple, made sure all indexes that effected by query including clustered indexes where rebuilt after each DataWarehouse data load. That made things better most of the time.
What fixed it permanently was putting NOLOCK locking hints .
This seemed to resolve everything, but if it does not for you, try doing a table sp_recompile after your datawarehouse data load jobs complete.