Press "Enter" to skip to content

Category: Error Handling

Finding SSAS Tabular Dimensions in Excel

Olivier Van Steenlandt has lost a few dimensions in the couch cushions:

A colleague reached out last week while connecting to one of our SQL Server Analysis Services models in Excel. He couldn’t find the expected Attribute folders in the model. He was looking for the following dimensions:

Of particular interest was that this colleague could not see them but Olivier could. The answer ends up being a bit surprising.

Comments closed

Azure Blob Storage Operating System Error 86

Jose Manuel Jurado Diaz 86’d that option:

Today, I worked on a service request that our customer got the following error message: Cannot open backup device ‘https://XXX.blob.core.windows.net/NNN/YYY.bak‘. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it. 

Read on to get a better understanding of what this error actually means and how you can fix it.

Comments closed

Error Trapping with Extended Events

Chad Callihan wants to know what’s going wrong:

Many times, when I’m using Extended Events, I’m filtering on an Id or procedure name in query text and tracking what’s hitting a database. In these cases, I’m assuming queries are going to complete successfully. Did you know Extended Events can be useful when you’re expecting things to go wrong? Let’s look at how we can use Extended Events to track down error messages.

You’ll get a fair amount of noise on a busy server–especially dev servers where people are writing queries and trying things out—but this is a great technique for seeing if something has gone wrong and we need to look into it before a customer reaches out.

Comments closed

TINYINT Casts in Spark SQL vs T-SQL

Bill Fellows runs into an interesting oddity:

Yet another thing that has bitten me working in SparkSQL in Databricks—this time it’s data types.

In SQL Server, a tinyint ranges from 0 to 255 but both of them allow for 256 total values. If you attempt to cast a value that doesn’t fit in that range, you’re going to raise an error.

SQL Server’s TINYINT data type is an unsigned one-byte number, whereas TINYINT in Spark SQL is a signed one-byte number. But that’s not the biggest difference Bill finds, so check out the post to learn more.

Comments closed

Troubleshooting a Downed SQL Server

Kevin Hill takes us through an issue:

This is a stand-alone bare metal server located at a hosting facility with some local storage as well as NAS storage.  SQL Server 2016 Standard Edition, 8 cores, 128GB RAM.  The primary database is nearing 1TB and is has multiple filegroups (Active, Archive, Indexes, etc.).   This database and apps that touch it ARE the company.

Read on for Kevin’s process, which was a solid bit of troubleshooting.

Comments closed

Australian Azure Downtime After-Action Report

Brent Ozar shares some thoughts:

Note that 11:34, the decision was made to shut down infrastructure without Microsoft failing your databases over elsewhere. If you were an Azure SQL DB or Cosmos DB user, and you weren’t paying for replicas in another data center, it was up to you to follow Microsoft’s disaster recovery guidance.

Controversial opinion: I actually love that and I think it’s great.

That is definitely a controversial opinion, but it’s also one I agree with. Read on for more of Brent’s thoughts.

Comments closed

STRING_SPLIT Not a Recognized Built-In Function Name

Kendra Little resolves an error:

Yesterday, I was writing some Transact SQL to dust off the cobwebs. I got confused when I was playing around with the STRING_SPLIT function, and kept getting the error:

Msg 195, Level 15, State 10, Line 2 ‘STRING_SPLIT’ is not a recognized built-in function name.

When I saw this, I immediately thought compatibility level, as that’s something I’ve run into several times before. In this case, it turns out that the correct answer was something else.

Comments closed

Executing Transactions in PostgreSQL

Salman Ahmed rolls it back:

Transactions, like any other database, are a key component of PostgreSQL. A transaction is a sequence of one or more database operations that are executed as a single unit of work. These operations can be queries (e.g. SELECT, INSERT, UPDATE and DELETE) that modify the database’s state.

A transaction’s main purpose is to combine multiple statements into an atomic, all-or-nothing process. It ensures that either all operations within a transaction are fully completed, or none of them are executed at all. Concurrent transactions cannot see each other’s unfinished changes. Updates from ongoing transactions remain hidden until completion, at which point all changes become visible simultaneously.

This is very similar to SQL Server, except their savepoints actually work they way they’re supposed to.

Comments closed

CREATE OR ALTER and sp_ Procedures

Louis Davidson hits an edge case:

It isn’t that such objects are to be completely avoided, it is that they are ONLY to be used when you need the special qualities. Ola Hallengren’s backup solution creates a dbo.sp_BackupServer procedure so you can run the backup command from any database.

But if you don’t need the special properties of the sp_procedure, they are bad for the reasons Aaron stated, the reason I stumbled upon today being just a special subset. In this case CREATE OR ALTER behaves differently than CREATE in a way that was really confusing to me as I was working on a piece of code today.

Read on for Louis’s tale of woe and confusion, but mostly confusion.

Comments closed

External Table is Not in the Expected Format for Power Query

Chris Webb handles an error:

Sometimes when you’re importing data from files using Power Query in either Power BI or Excel you may encounter the following error:

DataFormat.Error: External table is not in the expected format

What causes it? TL;DR it’s because you’re trying to load data from one type of file, probably Excel (I don’t think you can get this error with any other source but I’m not sure), and actually connecting to a different type of file.

Read on for an example, a more detailed description of when you’d get the error, and how to fix it.

Comments closed