Press "Enter" to skip to content

Category: Error Handling

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

TaskFactory Activation on an Azure-SSIS Integration Runtime

Andy Leonard does some sleuthing:

I regularly help customers migrate SSIS to Azure-SSIS integration runtimes, a nifty component of Azure Data Factory. I was recently stumped by an error activating TaskFactory (Task Factory for the search engines…) on an Azure-SSIS IR node. The error was:

“The system cannot find the file specified.”

Read on to figure out where the file is and how to fix this error.

Comments closed

Restoring SSAS Cubes to SQL Server 2022 CU5

Meagan Longoria runs into a problem:

I have a client who was upgrading some servers from pre-2022 versions of SQL Server to SQL Server 2022 CU7. They had some multidimensional SSAS cubes that were to go on the new server. But they ran into an issue after the upgrade. After restoring a backup of an SSAS database to the new server they found that they could no longer modify the data source using SSMS.

Read on to see what the problem was, as well as how to fix it.

Comments closed

SQL Server Events: Attention Signal and User Error Message

Jose Manuel Jurado Diaz explains the difference:

Several days ago, I got a service request where we had a conversation about the differences about SQL Server Events: Attention Signal and User Error Message. In the realm of SQL Server, a well-rounded understanding of various events is crucial for optimizing performance, troubleshooting issues, and maintaining a robust database environment. In this article, we’ll delve into three important events: “Attention Signal,” “User Error Message,” and “Command Timeout.” We’ll explore their significance, scenarios where they occur, and provide a practical script to simulate and capture these events. Let’s dive in!

Read on to understand the intent of each of these event types.

Comments closed

ALTER TABLE SWITCH and Errors 4907, 4908, and 4912

Eitan Blumin works out some problems:

When it comes to managing tables and indexes in SQL Server, the ALTER TABLE SWITCH statement is a powerful tool for “moving” data swiftly between tables. However, this convenience can sometimes be met with frustrating roadblocks, such as errors 4907 and 4908.

These errors may be confusing about their underlying cause, particularly when the source and target tables have identical partitions, including in non-clustered indexes.

Read on to see what these error messages mean and how you can correct them.

Comments closed

Thoughts on Parameter Sensitive Plan Optimization

Jared Poche shares some thoughts:

First, I wanted to point out that there are fixes to issues specific to Parameter Sensitive Plan Optimization in SQL Server 2022. The issue itself is documented at the link below.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver16#known-issues

When I heard about the issue, I heard that it could cause SQL Server 2022 to cause stack dumps every 15 minutes. The time interval made me think it was related to the default interval for Query Store, and that seems to be the case.

Jared has several thoughts on the topic as he puts together a talk on PSPO.

Comments closed

Tips for Limiting Redis Failures

Phil Booth provides the ammo and we provide the feet:

Production outages are great at teaching you how not to cause production outages. I’ve caused plenty and hope that by sharing them publicly, it might help some people bypass part one of the production outage learning syllabus. Previously I discussed ways I’ve broken prod with PostgreSQL and with healthchecks. Now I’ll show you how I’ve done it with Redis too.

For the record, I absolutely love Redis. It works brilliantly if you use it correctly. The gotchas that follow were all occasions when I didn’t use it correctly.

My one addition here is to be really careful if you use Redis as persistent storage rather than a cache. Redis as a cache is easy: if the server goes down or you have trouble, you simply have more database calls than normal. Redis as persistent storage is a much more complicated beast which seems to fall over a lot more often and is significantly more finicky about drivers.

Comments closed

Conversion from XML to Target Collation Impossible

Chad Callihan has a mission, should you choose to accept it:

I recently ran into an error for a query that was casting XML to VARCHAR to search for a value. I have a bit of familiarity with the XML for these types of queries, so I was surprised when running the SELECT statement caused this error, which I had never seen before:

Msg 6355 “Conversion of one or more characters from XML to target collation impossible”

Click through for the solution. The real solution? NVARCHAR Everywhere.

Comments closed

Service Broker and the Unstoppable SQL Server Instance

Sean Gallardy shuts it all down:

I was sent a quite an interesting email stating that SQL Server would not shut down. Attempting to stop the service via services or the SQL Server Configuration Manager resulted in a timeout with SQL Server still running. Trying to execute the shutdown with and without NOWAIT T-SQL command resulted in the same, the process still running. Seems quite weird that SQL Server just refuses to shutdown!

Click through for Sean’s investigations, what the result was, and how the customer ultimately decided to deal with it.

Comments closed