Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSRS report returning incorrect results on first run Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:45 AM
Points: 4, Visits: 102
Hi

I 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.
Post #1593525
Posted Thursday, July 17, 2014 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:29 PM
Points: 10,260, Visits: 13,230
My first thought was that the report was using a cached version of the report, but that wouldn't be the behavior you are describing and it wouldn't explain why a direct call to the SP does the same thing.

I've never seen this happen on any version of SQL Server. Have you tried capturing the execution plan and seeing if it changes from run to run? That shouldn't cause incorrect results, and if it does I'd say you've found a serious bug.

What is the isolation level used?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1593627
Posted Thursday, July 17, 2014 8:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
Are you by chance using NOLOCK in your stored procedure?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1593641
Posted Thursday, July 17, 2014 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:45 AM
Points: 4, Visits: 102
Hi

The WITH (NOLOCK) hint isn't being used in the SELECT statement.

Regards

Mark
Post #1593678
Posted Thursday, July 17, 2014 9:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
DataTrim (7/17/2014)
Hi

I 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.
Post #1593679
Posted Thursday, July 17, 2014 9:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
DataTrim (7/17/2014)
Hi

The WITH (NOLOCK) hint isn't being used in the SELECT statement.

Regards

Mark


Okay... ummmm.... Was this a test? Are you making a confession? Are you saying that you tried this with no results?
Post #1593680
Posted Thursday, July 17, 2014 9:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
Is there something else other than a select statement in that proc? Trying to gauge if nolock is used in the proc at all.


And back to Jacks question about isolation levels. Is the proc setting an isolation level within it? What is the isolation level set at for the database(s) in question?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1593682
Posted Thursday, July 17, 2014 9:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 8:59 AM
Points: 341, Visits: 307
SQLRNNR (7/17/2014)
Is there something else other than a select statement in that proc? Trying to gauge if nolock is used in the proc at all.


And back to Jacks question about isolation levels. Is the proc setting an isolation level within it? What is the isolation level set at for the database(s) in question?


If it is setting Isolation level in the sproc that would explain why running it in SSMS could have this effect.
Very large Datawarhouse databases can take too long to populate these things so I use the (NOLOCK) statements that do something much different than your default Snapshot Isolation levels on SQL 2005.
Post #1593686
Posted Friday, July 18, 2014 4:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:45 AM
Points: 4, Visits: 102
Thanks to everyone who has replied to my question. To answer some of the questions that have been raised:
- The default database isolation level is set to Read Committed
- No isolation level is being set in the stored proc
- No Lock is not used in the stored proc

So far I have tried adding in NOLOCK to the stored proc and this hasnt stopped the incorrect results on first run. I have also compared the exection plans between 1st and 2nd runs and they are both the same. I'm just going through the process of checking all the indexes are present and being rebuilt after the warehouse rebuild.

Thanks again

Mark
Post #1593978
Posted Friday, July 18, 2014 7:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
Based on all of this, can you provide DDL and DML that would reproduce the issue? Since the issue occurs when running the stored proc alone, there is no need to include a report. But having a look at the query that can cause the issue will provide tons of help.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1594052
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse