cannot read the next data row for dataset x
-
Hi, a peer asked me today to help on this ssrs error. There is a lot going on in this report but the dataset in question uses a table meant just for ssrs and is filtered in the report by the userid of the person running the report. When i run the query with a valid userid or without in ssms i get the expected results. But the report gets the error you see below even when i rig it to use the only userid currently present in the source table. i poked around on the internet and the only culprits i see have to do with data types. but i dont believe a data type has anything to do with this as the only variables are 1) userid passed from ssrs, 2) 2 table variable which dont appear to interfere when i run the query under ssms with and without a valid userid. does the community recognize this error as having anything to do with something other than data types?
- This topic was modified 1 week, 6 days ago by stan.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
here is one of the posts i see on this subject. its hard for me to make the leap between a division and sql being unable to read a (next) row. https://stackoverflow.com/questions/57414474/rsprocessingerror-reporting-services-error-rserrorreadingnextdatarow .
here is another one. i'm going to look closer at my report for some sort of intentional or unintentional cast. https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180809 .
i started dividing and conquering and believe i found the culprit. i ...
- hardcoded the userid parameter which is passed to the offending dataset as a filter on a table of manufacturing values. this was important to me because the table that is used to generate that dataset has only one userid value at this time over about 128 rows. this didnt help
- stopped passing the latter and instead hardcoded the userid value in the query. no help
- got rid of variable tables in favor of temp tables. no help
- got rid of two mathematical total like expressions at the bottom of the tablix in question. they occupied their own rows. they referred to the value in the first row of another dataset. no help
- tore apart a 3 part union all in the query by including only one query at a time. when added back the 3rd (which was most complicated of the 3) seemed to be the culprit but only brought down the report , not the same query running exactly as is in ssms.
- pulled out the 3rd query and instead inserted it into a new temp table planning to just union all the new temp table. and when i ran it into that new temp table caught the fact that a divide by zero was occurring i believe on an average (the divisor) that the query had cast to just 2 decimal positions.
so my take away was that ssms was forgiving the divide by zero but partnered with ssrs, sql wasnt. disturbing to say the least. i think there is a way in sql to be stricter on what is forgiven, i believe it is called exact abort or maybe arithmetic abort..
my plan after comparing notes with my peers is simply to either cast that value to more decimal places or show a blank in the report when the divisor is so small and causing the division to generate a value that is too high to be useful to our business.
-
Got some details? Like the parameter values you're trying to pass, the parameter definition, the stored procedure text, the related column types and sizes... you know, something so we can actually help?
Maybe show us the parameter values and types you're trying to pass and some data maybe?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply