Press "Enter" to skip to content

Category: Error Handling

Finding Faulty Rows in Tabular Server Errors

Published 2022-10-18 by Kevin Feasel

Teo Lachev goes error-hunting:

A scheduled SSIS job that executes a massive DAX query to an on-prem Tabular server (Power BI can also generate this error) one day decided to throw an error “Source: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2020, 98) Calculation error in measure ‘Account Snapshot'[Average utilisation % of all CR active current accounts last 3 months]: The result of a conversion or arithmetic operation is either too large or too small.” At least we know the offending measure, but which row is causing the error? The query requests some 300+ measures for 120 million customers, so I thought someone might find the troubleshooting technique useful. Let’s ignore what the measure does for now except mentioning that it performs a division of two other measures.

Click through for the technique.

Comments closed

A Duplicate Despite a Distinct

Published 2022-10-13 by Kevin Feasel

Murder Forrest McDaniel Wrote:

“A duplicate despite a DISTINCT bodyguard…” one mutters. “This isn’t the normal level of foul play.”

Mods arrive to cordon off the scene. Twelve different butlers have already been accused, but each has a solid alibi, even MAXDOP.

Generic Protagonist paces back and forth. They’re waiting for the forensics report. Finally it arrives.

Of course, those forensics people don’t know what they’re doing, so you’d better click through and find the real killer.

Comments closed

When Batching Breaks Down

Published 2022-10-11 by Kevin Feasel

Erik Darling has some thoughts on batching data modifications:

The whole point is to go a little bit easier on your server:

– Smaller transactions sent to the log file

– Shorter locking durations

– Less build up of the version store (optimistic isolation levels, triggers, etc.)

But there are thing that make batching impossible, or downright tricky.

Erik provides more details after the jump. This is all “stuff you should think about” rather than “reasons why you shouldn’t batch.”

Comments closed

Failed to Update Server Firewall Rules in Azure

Published 2022-10-10 by Kevin Feasel

Andy Leonard gets an error:

Recently, I attempted to update the Networking for an Azure SQL Server I use when delivering training. Specifically, I was attempting to set “Public network access” to “Selected networks,” add my client IP, and check the “Allow Azure services and resources to access this server” checkbox – found on the Networking blade for my new Azure server:

Fortunately, this was a case of “read the actual error message, understand the actual error.” It doesn’t always work out that way but it’s nice when it does.

Comments closed

Azure SQL MI Error Loading Backup Retention Policies

Published 2022-09-28 by Kevin Feasel

Paloma Garcia Martin troubleshoots an error:

When you try to create a new database (*) using Azure Portal using non supported characters, you will see an error indicating characters that you cannot use on the database name.

But if you use SSMS tool, it doesn’t include these characters cheeking and it will not avoid you to use these non-supported characters on the database name. 

Click through for an example of this error in action.

Comments closed

Case-Sensitive String Comparisons and Case-Insensitive Tables

Published 2022-08-22 by Kevin Feasel

Meagan Longoria reminds us that case sensitivity was a huge mistake:

Here’s the scenario: You are using Python, perhaps in Azure Databricks, to manipulate data before inserting it into a SQL Database. Your source data is a flattened data extract and you need to create a unique list of values for an entity found in the data. For example, you have a dataset containing sales for the last month and you want a list of the unique products that have been sold. Then you insert the unique product values into a SQL table with a unique constraint, but you encounter issues on the insert related to unique values.

Click through for an example and how to extricate yourself from this scenario. Python certainly is not the only language to do this, so it’s good to know even if you don’t plan on using or supporting Python.

Comments closed

Resolving tempdb Issues in Azure SQL DB

Published 2022-08-12 by Kevin Feasel

Holger Linke troubleshoots some problems:

The tempdb system database is a global resource available to users who are connected to Azure SQL Database or any instance of SQL Server. It holds temporary user objects that are explicitly created by a user or application, and internal objects that are created by the SQL Server database engine itself. The most common tempdb issue is running out of space, either regarding tempdb’s overall size quota or the transaction log.

The available tempdb space in Azure SQL Database depends on two factors: the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database. These are also the main factors to control if you are running out of tempdb space.

Click through for several error cases and how we can resolve them.

Comments closed

Neo4j Imports and Case Sensitivity

Published 2022-08-09 by Kevin Feasel

Steve 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 closed