Home Dashboard Directory Help
Search

rsInvalidExpressionDataType or 'invalid index' errors can be caused by data cache files by Robert Heinig II


Status: 

Closed
 as Fixed Help for as Fixed


4
0
Sign in
to vote
Type: Bug
ID: 683147
Opened: 8/5/2011 6:50:40 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

SSRS 2008 R2 (at least RTM, CU3, CU5 and CU7) report design in BIDS produces data cache files that cause a wide variety of problems. 2008 R2 fails to discard these cache files more frequently than 2005 did.

The particular example I'm attempting to document here demonstrates that data pulled from these caches is pulled by field index, not by name. Replacing the SQL text in a dataset with a different SQL that pulls stuff from different tables, but where the first field has an identical signature will cause BIDS to pull data from the stale cache file and attempt to interpret it according to the new metadata. The error code in the issue title results when the cache file contains non-numeric text in an ordinal position where the new SQL would deliver an int, or when the cache contains less columns than the new SQL would deliver and those rightmost columns are attempted to be used.

BIDS even does one better: I *deleted* the whole dataset and created a new one with a different name and different SQL and BIDS still pulled data from the stale cache!

There's a curiosity: The "shifted" data, if one looks at my example closely, is still one off. This might correspond to the bug where, if a sp-bound dataset is refreshed when the SP has added new fields in the middle of the list, BIDS will place most new fields displaced in the dataset, and the distance from the proper positions as delivered from the SQL is a regular sequence, e.g. inserting 5 new fields at position 10 might put the new fields at -2 off, -1 off, correct, +1 off, +2 off...

The design decision to use cache files at all should be reevaluated. Also, 2008 R2's BIDS must learn to discard cache files whenever the "Refresh fields" button is used, whenever the SQL textbox is changed (even when typing space-backspace at the end), or any other change to the dataset properties is attempted.
Details
Sign in to post a comment.
Posted by Robert Heinig II on 3/27/2012 at 8:50 AM
Oh, I forgot to hint at the open tool I use to ease this: BIDS Helper over on bidshelper.codeplex.com. Whenever you change a piece of SQL related to your report, or hit refresh fields - Context menu of the project, three clicks, done.
Posted by Giacomo Rovai on 11/18/2011 at 1:02 AM
I've SQL Server 2008 R2 SP1 and for me it's not fixed. I've exactly the problem described. What can I do?
Posted by Microsoft on 9/26/2011 at 5:19 PM
Thanks for your feedback. We believe this is fixed in SQL 2008 R2 SP1.

Bob Meyers
SQL Server Reporting Services
Posted by Robert Heinig II on 8/23/2011 at 12:46 AM
If it's fixed, care to document which minimum KB/CU/SP is required to get the fixed version?
Sign in to post a workaround.