SQL Server Reporting Studio report showing "ERROR#" or invalid data type error
Solution 1
Found the answer here.
Basically, it's a problem with caching and you need to delete the ".data" file that is created in the same directory as your report. Some also suggested copying the query/report to a new report, but that appears to be the hard way to achieve the same thing. I deleted the .data file for the report I was having trouble with and it immediately started working as-expected.
Solution 2
After you preview the report, click the refresh button on the report and it will pull the data again creating an updated rdl.data file.
Solution 3
Another solution to this issue is to click Refresh Fields in the Dataset Properties menu.
This will update the list of fields, and force SSRS to get new data, rather than relying on a cached version.
Related videos on Youtube
Josh
BY DAY: I'm a web application developer and team lead for State Farm Bank. BY NIGHT: I relax with my family and maintain boat dealer software. FOR FUN: I garden, fish, maintain 50 acres, and hang out with my wife and daughter.
Updated on June 26, 2022Comments
-
Josh almost 2 years
I struggled with this issue for too long before finally tracking down how to avoid/fix it. It seems like something that should be on StackOverflow for the benefit of others.
I had an SSRS report where the query worked fine and displayed the string results I expected. However, when I tried to add that field to the report, it kept showing "ERROR#". I was eventually able to find a little bit more info:
The Value expression used in [textbox] returned a data type that is not valid.
But, I knew my data was valid.
-
wruckie about 10 yearsmark your answer as the answer
-
Josh about 10 yearsI have to wait 2 days.
-
-
Bengie about 9 yearsI changed a parameter, which invalidates the cache. It worked for me.
-
Zach Wymer over 8 yearsThis does not work. I clicked "Refresh Fields" and changed the name of the returning column. No dice. Deleting .data is the way.
-
YtramX almost 7 yearsThank you so much! This was driving me insane!
-
Mathews Sunny about 6 yearsThis is just of comment quality
-
JosephDoggie over 5 yearsSometimes also closing / re-opening VS studio is required as well.
-
phn over 4 yearsJust to be clear - you need to delete the .rdl.data file that belongs to the report. I spent some time looking for a file called .data before I worked it out.After that it works - thanks!
-
Benzo over 3 yearsI want to know why the visual studio "clean" function doesn't remove these cached files, Like I would expect a "clean" process to do.
-
jw11432 about 3 yearsMakes absolute sense. I was actually wondering how my query, which took some time to load, could so instantly spit out results in my report each time. The caching as it pertains to the
.rdl.data
file completes the picture in my head. Fantastic and valuable information, thank you!