A Fine Slice Of SQL Server
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.
Comments closedKai 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 closedEmanuele 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 closedJoe 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 closedKevin Wilkie breaks out the catcher’s mitt:
In our last post, we started talking about transactions and how they can help your databases have data integrity. Let’s continue that today with our friends TRY and CATCH.
TRY is just telling SQL Server that we want to try out something. CATCH tells SQL Server what to do if it completely messes up.
Read on to see how it works. As a quick note for those of you who come at this from a software developer’s background, T-SQL’s try-catch capabilities are not as robust as what you probably are used to, especially with respect to what gets caught. Most severe error messages are not catchable, so you can’t always expect that database query to go quietly in the night.
Comments closedChris Webb explains an error message:
If you’re using DirectQuery mode in Power BI you may occasionally run into the following error message:
OLE DB or ODBC error: [Expression.Error] We couldn’t fold the expression to the data source. Please try a simpler expression..
What does it mean and how can you fix it?
Read on for the answer, as well as a quick depiction of query folding.
Comments closedARM templates are widely used when we need to repeatedly deploy solutions/infrastructures in the cloud. Leveraging the concept of infrastructure as code ARM templates are a powerful resource to ease our daily job, however we might face some challenges when using them.
When we are creating several resources within the same template – using Json or Bicep – it’s crucial to make sure that all resources are created in the right order, ensuring that all depending on resources are fully provisioned before you move to the next operation.
Error (internal server errors) and conflicts can occur during our ARM template deployment and it could be difficult to troubleshoot or understand the root cause of them.
Read on for one annoying error and its fix.
Comments closedErik Darling looks disapprovingly:
So if you execute the above proc, all it returns is an empty result with no error message.
Kinda weird.
Like not having finger or toenails.
I’m dating myself here but I remember a comment in a TheDailyWTF thread in which somebody puts an ASCII art Pikachu in every empty catch block because hey, you gotta catch ’em all.
Comments closedReitse Eskens does some troubleshooting:
For those who are not familiar with Always Encrypted: it’s a built-in technique in SQL Server where data gets encrypted in a random or deterministic manner with a certificate and an algorithm. Long story short, without the certificate it’s gobbledygook. If you want to read more, check out this link.
As mentioned, the encryption was working perfectly, no-one could read the data in a usable way in the application. The point was that the application should be able to do that. And so we took up the challenge to see where things went wrong.
I’ve found that data is much more secure when nobody can ever see it again. That’s why I store all of my data in /dev/null. It’s also extremely fast.
Comments closedGreg Dodd diagnoses a problem:
I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error:
The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’.
Read on to see what caused this error.
Comments closed