I’m an accidental SSRS / Power BI Report Server / PBI.com Administrator, along with my other do-all-things-data duties. It’s been a hot minute (cough years) since I’ve messed around in SSRS and I’m relatively new to PBI – both from the reporting and the administering side. Years ago I did some work with Tableau, but my most recent work in that realm was more R and Python based. Suffice to say – I run into issues a lot. Particularly with PBI Report Server (PBI RS).
Pssst – hint – PBI Report Server is basically the same as SSRS, so if you find some obscure problem and everything you google results in regular PBI answers, try googling the same problem with SSRS. Different database name, same almost everything else. YOU’RE WELCOME. You have no idea how much time I’ve just saved you.
That said, this specific error I came across was in SSRS, but really you could get this error from any of the reporting products.
You’ may receive an error that vaguely resembles this:
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset <objectname> (rsErrorReadingNextDataRow).
For more information about this error navigate to the report server on the local server machine, or enable remote errors
While there are probably MANY reasons you may receive this error, I’m going to talk about one here: XML. That’s right, your old frienemy XML.
In our case, we were getting XML documents from AX/various sources, and storing them in another database in an ntext column. <insert obligatory “I didn’t build it” disclaimer>. So when another process was pulling it … say for a report... it was barfing all over the place. Running a snippet of sql that the report was using, resulted in a more helpful message:
Msg 9402, Level 16, State 1, Line 15
XML parsing: line 1, character 38, unable to switch the encoding
What’s in line 1 of all these xml documents? The XML Prolog.
You wanna know something else? It’s optional. And that pesky little UTF-8 encoding is causing all of your woes. My suggestion: find what is adding the prolog to your XML docs and get rid of it. (Unless it’s a person, then just tell them to cut it out.)
There are alternatives you can do, but I wouldn’t recommend them. I’ll list some here in case you want to experiment (I didn’t.) You could clean the data and remove the prolog (aka declaration or header information), but that’s a messy solution that will probably have to be changed over time. (More than you would think at first glance.) If you are on SQL Server 2019 or above, you could also consider using the UTF-8 encoding support they introduced – even at the column level – but that could have unintended consequences, and again, unplanned maintenance down the road. Personally, I have enough to do – thank you very much. (If you want to investigate this route here’s a handy dandy little article Introducing UTF-8 support for SQL Server. )
If you aren’t dealing with an ntext column, then you may have some additional alternatives here: XML Parsing – Unable to switch encoding using utf-8 when casting from NVARCHAR. But again, you run into the issue of changing column types/lengths and from personal experience, that can cause other big headaches. Don’t say you weren’t warned.
That’s it. I didn’t really plan on making my first kinda techie article to be about SSRS/PBI/XML – but it happened to me today and now it is yours. Use it as you want or stuff it in a turkey.
Speaking of turkey: my daughter drew a great pic last night of a turkey that didn’t look exactly like a turkey because she “wanted to draw a turkey that people wouldn’t want to eat”. Unfortunately it got messed up and I can’t share it with you. As a poor substitute, I give you a turkey from Summer of 2021 that was contemplating his attack on ME.
Hope you all have a wonderful Thanksgiving holiday!
–Data Work Mom
Leave a Reply