#TidBitTrenches: SSMS Errors on Load

New install of SQL Server 2019 produced an interesting error upon opening SSMS.

Ran into an interesting issue today. Upon opening a new install of SQL Server 2019, I received the below error:

SSMS Error message. Text of error message below image.

The Microsoft.SqIServer.Management.ThreatDetection.ThreatDetectionPackage,
Microsoft. SqIServer.Management.ThreatDetection,
Version=, Culture= neutral,
PublicKeyToken=89845dcd8080cc91’ package did not load correctly.
The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file C:\Users\<username>\AppData\Roaming\Microsoft\AppEnv\1 5.0\ActivityLog.xml‘.
Restarting Visual Studio could help resolve this issue. Continue to show this error message?

UGH. Not helpful. I do what any reasonable IT person does – I google the error. Not super helpful either. I look back at the error message. Wait a minute. That version number looks odd. Since this is a new box, I look at what has been installed.

Installed apps image highlighting 2 versions of SSMS

2 versions of SSMS? That in itself is not normally an issue, but it was the Preview version that gave me pause. Went to the authoritative voice – Microsoft – and said:

SSMS 19.0.1 is the latest general availability (GA) version. If you have a preview version of SSMS 19 installed, you should uninstall it before installing SSMS 19.0.1.

Ok. Fine. I’ll follow the rules. This once. I uninstalled both versions and then installed the most current version.

Gene Wilder gif from Young Frankenstein with text: "Its Alive" From https://media.tenor.com/SuADVxKkQ-AAAAAC/frankenstein-its-alive.gif

And just like that, I was back in business. Not even a reboot needed.

SSMS window without errors this time.

PBI: Report couldn’t access the data source

PBI data source error: Sometimes the easiest thing to check is actually the cause of the problem.

You’ve opened a report in PowerBI Service and you get the dreaded “This report couldn’t access the data source. Contact <author name> the author, to have it fixed.”

Image of the error message.

As we are expanding our report offerings and learning more with PBI Service, we get this message a lot. Often it’s a security issue, and this post isn’t about that rabbit hole, but rather a short and sweet reminder about a preliminary check that is often overlooked: is your data source up and running?

A lot of times in our Dev environment we’ve had something go wonky with our tabular model and need to re-Process the database. (That’s what dev environments are for – right?) This is what happened this morning when one of our developers reached out with this error message. As I first started heading down the security route, I paused and decided to check the database first. Ding-dong! Nothing weird in PBI, it was our tabular model. A quick checked of a few tables in the cube confirmed that it wasn’t giving anything to anyone. Reprocessing the cube and then refreshing both the data AND the page(s) that give the error message in PBI cleared everything up.

Moral of the story and to put a twist on Occam’s Razor: check the easiest thing first.

SSRS/PBI RS Error: Cannot read the next data row for the dataset

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.

Turkey about to attack me.

Hope you all have a wonderful Thanksgiving holiday!

–Data Work Mom

%d bloggers like this: