A Fine Slice Of SQL Server
Gilbert Quevauvilliers is a cultured individual:
I was recently working with an overseas customer and when I tried to open the PBIX file I got the following error
“Culture Name ‘en-IL” is not valid or is not supported”
Read on to understand what the problem was as well as how Gilbert was able to fix it.
Comments closedSteve Jones is getting me in a ranting mood:
I kept editing the file and trying different things. I compared what I had locally with what was on GitHub. Eventually, I realized this is the issue:
{employeeID:row.EmployeeID}
In the GitHub csv, the first row has headers with EmployeeID. In my local file, the header is “employeeID” (lower case). As soon as I edited this, it worked.
Case sensitivity is a big historical mistake.
Comments closedLee Markum notes a change in SQL Server 2019 CU 16:
As a data professional managing SQL Servers, you need to be thinking about Windows and SQL Server patching.
You want to stay up to date, but you also have to be careful because sometimes staying fully up to date comes with the risk that a new patch might break a feature you are using. This is possibly the case for SQL Server 2019 CU 16. If you’re using Log Shipping on a database that has TDE enabled and compression is enabled on those backups, then you need to take heed.
I’ll walk you through what can happen and what I’ve seen work to resolve the problem.
This is the tricky part of getting rid of service packs: when I think cumulative update, I don’t think “Here’s a thing that could break backwards compatibility with other SQL Server instances which have not been patched.” Read on to see an example of this in action.
Comments closedKristyna Hughes needs to clear out some errors:
End users that are not trained in data governance but are actively involved in maintaining a data set can easily make data entry mistakes or create inconsistent data types within columns. For example, you may have a column in the dataset called “Sales” and instead of 0, someone may type “None” or “NA”. When this gets loaded into Power BI, Power BI will not know how to convert the text value “None” to a number, and it will throw an error on the refresh of the report.
One way to mitigate the impact of user-entered data is to replace errors with null values. This is not ideal since it doesn’t fix the data entry issues, but it does enable reports to still be refreshed and used while the data issues are addressed. In Power Query, you can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors”.
Click through for a script which does this for all columns in Power Query.
Comments closedKamil Nowinski runs into some trouble:
Last weekend, I played a bit with Azure Synapse from a way of mounting Azure Data Lake Storage (ADLS) Gen2 in Synapse notebook within API in the Microsoft Spark Utilities (MSSparkUtils) package. I wanted to just do a simple test, hence I followed the documentation from Microsoft: How to use file mount/unmount API in Synapse.
Having an ADLS Account already created in a subscription – should be easy peasy, right?
Read on to understand when things might be a little more complicated than they seem. And more frustrating, once you see the cause of the problem.
Comments closedRichard Swinbank diagnoses a problem:
I encountered this error recently while using Azure Data Factory’s (ADF) new(ish) Script activity to run a SQL query. It took me a while to find a fix, and when it happened again two weeks later I’d completely forgotten it. With a bit of luck, writing it down will help me remember next time – and if it helps you too, great
Read on to see what the problem was and how Richard solved it.
Comments closedGilbert Quevauvilliers ran into a problem:
I recently got a new laptop and I had to install all my programs again. Everything was going as expected, except when I went to use ALM Toolkit, the program would not open.
I would click on ALM Toolkit, I would see it open for a few seconds in task manager and then it would disappear.
That led me down a few rabbit holes, I thought could it be Windows Defender, could it be the anti-virus or could it be installed incorrectly.
It turns out that neither of those was the problem. Read on to learn what the issue was and how Gilbert corrected it.
Comments closedNiels Berglund sees something odd:
Every 6 – 9 months (or so), I clean up my development PC just to keep it “lean and mean”. I do it by formatting the hard-drive partition the OS (in this case, Windows) is on, followed by a new install. Recently I had a four-day weekend here in SA. Four glorious days off, a perfect time to “nuke” my PC and re-install!
Off I go, everything goes to plan (Chocolatey is my friend), and after a while, I am done (or as done as one can be). At this stage, I needed to do my weekly roundup blog post for the week gone by, and as I had done some changes to the GitHub repo from my MacBook Pro, I wanted to do a
git pullin the repo directory for my blog. Part of the story is that on my dev PC, I have all my repos on a separate partition from the system partition, so the non-system partition was un-affected by the reformat (or so I thought). Imagine my surprise when doing thegit pullI got:
Click through to see the error and root cause.
Comments closedJon Morisi does some troubleshooting:
I just spent a long slog sorting out why I could not connect to my SSIS instance remotely. I work in a very secure environment requiring network approval for any and all ports. According to the following article, I was under the impression that a request to open incoming traffic on port 135, to a specific IP, would allow SQL Server Management Studio, on that specific IP, to connect remotely to SSIS:
After opening port 135, I was receiving the error message in the title of this article:
If you find yourself in this situation, read on to see how Jon was able to solve the problem.
Comments closedReitse Eskens has some routing issues:
To make sure the on-premises connection uses the VPN and the private endpoint, we need to make sure the on-premises DNS (it’s always DNS) recognizes the traffic and redirects it to the VPN connection. But whatever we tried on the firewall, the traffic kept going the wrong way. It did have something to do with the on-premises DNS setup in the end.
When we tried to connect to the Azure SQL instance on IP-address, it threw an error because the instance wasn’t found. You can only connect to it with the FQDN (dbname.database.windows.net)
Click through to see what the problem was and how Reitse solved it.
Comments closed