Error Handling – Curated SQL

Power BI: Visual has Exceeded the Available Resources

Published 2020-01-23 by Kevin Feasel

Chris Webb explains why you might see an error in Power BI:

This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.Please try again later or contact support. If you contact support, please provide these details.More details Resource Governing: The query exceeded the maximum memory allowed for queries executed in the current workload group (Requested 1048580KB, Limit 1048576KB).
The official Power BI documentation has similar advice to what’s shown in this dialog about what to do here, but what’s really going on?
The information in the “More details” section of the section dialog gives you a clue: in this case it’s resource governance. When you run a DAX query in Power BI it will always use a certain amount of memory; inefficient DAX calculations can cause a query to try to grab a lot of memory. In Power BI Desktop these queries may run successfully but be slow, but the Power BI Service can’t just let a query use as many resources as it wants (if it did, it may affect the performance of other queries being run by other users) so there is a resource governor that will kill queries that are too resource hungry. In the case of the visual above the query behind it tried to use more than 1GB of memory and was killed by the resource governor.

Read on to understand where these limits are and how you can modify them.

Failed to Open Loopback Connection

Published 2020-01-14 by Kevin Feasel

Steve Stedman diagnoses an error:

Recently while working on a SQL Server for a client I came across the following error when I attempted to view the SQL Server logs in SSMS.
From there I decided I needed to track this down, so I attempted to run the sp_readerrorlog stored procedure, which also failed, but it provided more informaiton.
The full error message was:
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
Error log location not found.

There are several potential solutions, and the one Steve landed on definitely was not my first thought.

Short Substrings and Computed Columns

Published 2019-12-31 by Kevin Feasel

Erik Darling gives us a story about computed columns that turns out not to be about computed columns at all (having thereby subverted our expectations):

The problem is that when I tried to index it:
CREATE INDEX dummy ON dbo.Users(DisplayNameComputed);
I got this error:
Msg 537, Level 16, State 3, Line 21 Invalid length parameter passed to the LEFT or SUBSTRING function.
And when I tried to select data from the table, the same error.

Click through to find the real query killer.

Comments closed

Diagnosing PolyBase Errors

Published 2019-12-31 by Kevin Feasel

Niels Berglund takes us through an odd “incorrect syntax” error with PolyBase:

What we see in Figure 1, incorrect syntax exception, is strange, as I have executed the same code in a SQL Server 2019 Big Data Cluster, (BDC), without any issues, and the forum poster executed the same in SQL Server 2019 Enterprise Edition also without any issues.
Ok, but what about creating an external table against a relational data source – where we do not need to define an external file format?

There is a straightforward answer as to why the specific error message pops up, but I agree with Niels that it’d be nice to have “here’s the problem and here’s the solution” types of error messages. The deeper you get into the product—especially the older Hadoop external data source—the worse the error messages get.

Comments closed

Making an Executable JAR from sbt

Published 2019-12-18 by Kevin Feasel

Sakshi Gawande walks us through two problems you might hit when building Scala projects into JARs:

Now, before going to see the solution we first understand why this problem occurs. When you uses Simple Build Tool Command ‘sbt package’, it creates a jar file that includes the class files from your source code and also the content from your src/main/resources folder.
But there are mainly two things which is important to execute jar file, are not included

Read on for these two things.

Comments closed

Finding Missing SQL Server MSIs

Published 2019-12-11 by Kevin Feasel

Annette Allen had a service pack installation go south due to missing MSIs:

I was recently doing a service pack, I’d run it on the entire test estate and half of the Production estate, I’d used Pinal Dave’s really useful AG check list and been really overcautious, I’d finished integrity checks on all databases, I’d backed everything up and even had a snapshot of the server completed.
When I clicked on the service pack I got the error “missing MSI” sorry I don’t have the screen dump or the full error message because at the time of trying to fix it I did’t think to take a copy

Read on to see what the root cause was and how Annette was able to fix this error.

Comments closed

Conversion Failed when Converting ‘NULL’ to Int

Published 2019-12-06 by Kevin Feasel

Kenneth Fisher has a fun error for us:

I love this error. Primarily because it demonstrates two very important things.
1. Errors matter. Make sure when you ask someone for help you give them the exact error and circumstances causing the error.
2. Experience matters. If you’ve been working with database development for a while you can probably pinpoint exactly what’s causing this error just from the error.
When I was handed this error from one of my co-workers I started by telling them exactly what was wrong, and then out of curiosity started a quick poll.

The answer is about as straightforward as it gets, and yet a pretty good percentage of people won’t get it on the first try.

Comments closed

Failing SQL Server 2019 Installation

Published 2019-11-27 by Kevin Feasel

Paul Randal walks through an installation error with SQL Server 2019:

I have a brand new Windows 10 laptop that I use solely for teaching, as the HDMI and SVGA connectors on my main laptop somehow have bad connections to the motherboard after a few years of traveling around the world. On the new laptop I have SQL Server 2017, and now that SQL Server 2019 RTM has shipped, and I’ve finished teaching for the year, I set out to install 2019 side-by-side with 2017 so I can move my teaching environment to 2019 and work on demos of the new features pertinent to what I teach.
Thinking this would be a smooth process, I kicked off the install wizard, went through it, hit go, and walked away. I came back half an hour later to see that all install steps had failed.

Read on for Paul’s solution.

Comments closed

Memory-Optimized Tables and Error Log Entries

Published 2019-11-27 by Kevin Feasel

Shaun J. Stuart points out impoliteness on the part of In-Memory OLTP:

It’s nice that they are labeled with [INFO], so you can be fairly sure they aren’t a major issue, but they still annoyingly fill up the log with information that is of no use to anyone outside of Microsoft. It would be nice if you could disable these messages but, to my knowledge, you cannot.

These are the types of error messages which should, by default, not write to the error log. My real bugbear is “Hey, we successfully backed up the transaction log!” You should not need a trace flag to turn that off; you should need one to turn it on for diagnostic purposes.

Comments closed

Debugging Azure Data Factory Data Flows

Published 2019-11-11 by Kevin Feasel

Mark Kromer takes us through debugging Azure Data Factory Data Flows:

When you are designing your mapping data flows in ADF, you are working against a live Azure Databricks Spark cluster. The size of that cluster is configurable via the Azure Integration Runtime. If you do not configure a custom Azure IR, then you will use the default Azure IR. That sets a very small cluster size by default of 4 cores for a single worker node and 4 cores for a single driver node. In most cases, while debugging and using data preview, that should be fine. But when you start exploring your data with column statistics or increase the sampling size in debug settings, you may find that you’ve exceeded the capacity on that small default cluster. Below are the steps you need to take to increase the size of your debug cluster.

Click through for step-by-step instructions.

Comments closed

Category: Error Handling