Error Handling – Curated SQL

Power BI Desktop External Tools Not Opening

Published 2022-06-30 by Kevin Feasel

Gilbert 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.

Leave a Comment

“Unsafe Repository” When using Git

Published 2022-06-28 by Kevin Feasel

Niels 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 pull in 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 the git pull I got:

Click through to see the error and root cause.

Leave a Comment

SSIS — RPC Server is Unavailable

Published 2022-06-24 by Kevin Feasel

Jon 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:
https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?redirectedfrom=MSDN&view=sql-server-ver16#BKMK_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.

Leave a Comment

Connecting to Azure SQL DB over VPN

Published 2022-06-24 by Kevin Feasel

Reitse 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.

Leave a Comment

Reading the SQL Server Error Log

Published 2022-06-17 by Kevin Feasel

Lee Markum has two ways to read the SQL Server error log:

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.
Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Click through for those techniques.

Leave a Comment

Error 13535: Data Modification Failed with Temporal Tables

Published 2022-06-14 by Kevin Feasel

Bob Dorr troubleshoots an issue:

When 2 or more workers are modifying the same row, it is possible to encounter 13535. The time of the begin transaction and the modification are the defining properties. When the transaction begin time is before the latest row modification, error 13535 is encountered.

Click through for an example of how you might trigger this error. This ultimately is the optimistic concurrency problem: how do you deal with multiple writers when using snapshot-based optimistic concurrency? Silently clobber or raise an error? Looks like temporal tables, like memory-optimized tables, raise an error instead of going quietly into the night.

Leave a Comment

Using SELECTEDVALUE with Field Parameters

Published 2022-06-13 by Kevin Feasel

Marco Russo diagnoses an error:

If you try to use SELECTEDVALUE on the visible column of the table generated by the Fields Parameters feature in Power BI, you get the following error:
Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Read on to understand what causes this error and what you can do to resolve it.

Leave a Comment

Alternatives to the Dead Letter Queue in Apache Kafka

Published 2022-06-03 by Kevin Feasel

Kai Waehner can’t return to sender:

This article focuses on the data streaming platform Apache Kafka. The main reason for putting a message into a DLQ in Kafka is usually a bad message format or invalid/missing message content. For instance, an application error occurs if a value is expected to be an Integer, but the producer sends a String. In more dynamic environments, a “Topic does not exist” exception might be another error why the message cannot be delivered.
Therefore, as so often, don’t use the knowledge from your existing middleware experience. Message Queue middleware, such as JMS-compliant IBM MQ, TIBCO EMS, or RabbitMQ, works differently than a distributed commit log like Kafka. A DLQ in a message queue is used in message queuing systems for many other reasons that do not map one-to-one to Kafka. For instance, the message in an MQ system expires because of per-message TTL (time to live).
Hence, the main reason for putting messages into a DLQ in Kafka is a bad message format or invalid/missing message content.

Read on to learn the Kafka-based approach to dealing with bad messages rather than using a Dead Letter Queue.

Comments closed

Troubleshooting Firewall Issues with Azure SQL MI

Published 2022-05-20 by Kevin Feasel

Emanuele Meazzo sees a problem pop up regularly:

Here is something that will save you lots of time and headaches when trying to connect to Azure SQL Managed Instances, especially from onprem servers or from other clouds; I had to repeat this multiple times to multiple actors, so I know it will happen to someone else too.
In most cases, “Connect Timeout” and/or “Cannot open server xxx requested by the login; Login failed” errors are caused by the firewall configuration and a lack of understanding the SQLMI networking model, let me explain:

Read on for that explanation.

Comments closed

T-SQL Order of Execution and Aliases

Published 2022-05-19 by Kevin Feasel

Joe Billingham explains why you can’t do that thing you want to do:

So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘.
The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it?

Read on for the answer. This is why some people I know have wanted a SQL-like language which runs in order of execution, so a query would start with the FROM clause rather than the SELECT clause. Languages like KQL do work that day, so there are examples in the wild.

Comments closed

Category: Error Handling