October 25, 2024 at 6:10 pm
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?
October 26, 2024 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 7, 2024 at 9:06 pm
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 ...
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.
November 7, 2024 at 9:18 pm
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?
November 13, 2024 at 5:15 pm
thx pietlinden and sorry for the delay as i was travelling and temporarily distracted.
i am convinced of what the problem was and think maybe i now understand what MS's bizarre message means. "Cant read next row" probably means the previous row had a problem so i (t-sql) am not going to try getting more rows. It would have been nice if i simply got a "divide by zero" (see below) message for that previous row. none of us had any idea what "cant read next row" means until maybe now.
And to make things worse, this report INSERTS into a table with a user id filter for the user running the report and for reading by the dataset that had the issue. It deletes anything for this user that might have been there previously. each time the report aborted, it rolled back those rows and i was fooled (in ssms) into thinking data from one other user was what was causing the offending dataset to abort. We tried to ask the user we were working with to tell us what userid he logged in with when the report aborted but the conversation went south. bottom line i thought ssms wasnt aborting (ie was forgiving) on the same query and data as what ssrs was. i was wrong.
What is happening is as follows...the original author (for cosmetic and simplicity reasons) wanted only the 1st 2 decimal places of a standard deviation to show. But instead of waiting for the appropriate time, he/she decided to do an early cast on that figure to decimal(18,2) which wouldn't have been terrible even for this std dev which is something like .000000004...... But unfortunately another statistic called Cpk (process capability index) was calculated by dividing one of 2 numerator choices by 3* that casted std dev.
what i did was to eliminate that cast and show the results (as ugly as they are) in a test ssrs report to create better communication between myself and the real statisticians/engineers. The Cpk value is astronomical and really meaningless in this situation when calculated using std dev = .000000004....
what they have decided then visually is for me to show "< 0.01" for the std dev rather than the way a float shows (exponent) otherwise in ssrs. and as a 2 decimal number otherwise. and either "> 4.0" or "N/A" in the Cpk text box if the number of data points is 10 or more and the value is large or the number of data points is less than 10. and to add a message below the grid explaining that Cpk cant be calculated for a sampling of less than 10 data points if indeed that was the case.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply