Press "Enter" to skip to content

Category: Error Handling

Configurable Retry in Microsoft.Data.SqlClient

Published 2021-03-23 by Kevin Feasel

Hasan Savran notes an improvement to the Microsoft.Data.SqlClient library:

You need to watch for Transient errors if you use SQL Server in Azure. Transient errors or Retriable errors can occur any time and your application should be smart enough to retry these failed operations. Azure might quickly shift hardware resources of your database to give you a better load-balance, when this happens your application might not be able to connect to the database. Since these reconfiguration events completes quickly, your application needs to be designed to handle these faults.This adds more complexity to your code because you need to write code to handle this manually. 

      Preview version of  Microsoft.Data.SqlClient library now supports RetryLogic function, you do not need to write any manual code to handle Transient or retriable errors anymore. 

Click through for more details as well as a demonstration. I’m surprised it took this long, to be honest—useful retry logic is exactly the type of thing which should be in the bowels of a library rather than littered throughout business code (or worse, not even in business code).

Comments closed

Bad Request when Debugging an Azure Data Factory Pipeline

Published 2021-03-15 by Kevin Feasel

Ed Elliott ran into a problem:

Now, whenever I am troublehooting something in Azure and I come to the activity logs I am always hopeful but also always dissapointed that they don’t show more details. The bit that really annoys me is that I know Micrsoft see more detailed error information as I have been screen sharing with a support tech who used log exporer to see more detailed error messages than I see – grrrr, just show us the data! Anyway, I digress – so in the activity log, does it give a clue as to what is wrong?

No, in a word no it doesn’t. 

Read on for the conclusion, which rates as “Should have been an easy fix but the error message was completely unhelpful.”

Comments closed

Row-Level Security and UseRelationship

Published 2021-03-10 by Kevin Feasel

Teo Lachev points out an issue when combining row-level security with the USERELATIONSHIP() function in a Tabular model:

You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:

The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.

Read on to learn what the issue is and one potential workaround.

Comments closed

Power BI: Parquet Files and Streamed Binary Values Error

Published 2021-03-08 by Kevin Feasel

Chris Webb explains an error:

If you’re using the new Parquet connector in Power BI there’s a chance you will run into the following error:

Parameter.Error: Parquet.Document cannot be used with streamed binary values.
Details:
[Binary]

This isn’t a bug or anything that can be fixed, so it’s important to understand why it occurs and what you can do about it.

Click through for the explanation.

Comments closed

The Importance of Configuring SQL Server Agent Alerts

Published 2021-03-08 by Kevin Feasel

Chad Callihan has a story in three acts:

Once upon a time, I was having a relaxing morning…until I received an e-mail alert about an Error Number 825 on a database. The error stated:

‘D:\sql_dat\DatabaseName.mdf_MSSQL_DBCC47’ at offset 0x00000004b9e000 succeeded after failing 1 time(s) with error: 121(The semaphore timeout period has expired.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

That’s not how you want your day to go. Fortunately, the database was not one that needed to be online 24/7 so I felt I could relax a little bit while starting to investigate. At this point, the issue seemed to be with one database.

Click through to see if everyone lived happily ever after.

Comments closed

Clarifying Key Errors on Graph Tables in SQL Server

Published 2021-03-05 by Kevin Feasel

Louis Davidson clears up some noise:

This causes the following error message:

Msg 2627, Level 14, State 1, Line 14Violation of UNIQUE KEY constraint 'AKEdge'. Cannot insert duplicate key in object 'dbo.Edge'. The duplicate key value is (455672671, 0, 455672671, 1).

So what is this: (455672671, 0, 455672671, 1)?

Click through to understand what this all means. Louis also has a quick procedure which looks up those details.

Comments closed

Avoiding Division by Zero

Published 2021-02-25 by Kevin Feasel

Chad Callihan has a few methods for us to avoid dividing by zero:

In the real world, everyone knows that if you divide by zero a wormhole will open up and swallow the universe. In SQL Server, it’s not good, but it’s not nearly as dramatic. I encountered the following error this week and thought it would make a good topic:

Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

There are multiple ways to handle this error message in SQL Server and some are better than others. Let’s take a look at a few.

Click through for those methods and try to keep this universe existent—it’s where I keep all my stuff.

Comments closed

ML Services: PYTHONHOME and PATH

Published 2021-01-11 by Kevin Feasel

Niels Berglund troubleshoots some issues:

In the last post, which looks at using Python 3.9 in SQL Server Machine Learning Services, I wrote this at the very end:

It looks like all is good, but maybe not? In a future post we’ll look at an issue we have introduced – but for now, let us bask in the glory of having created a new Python language extension.

In the post, we wrote a new language extension to handle Python 3.9, and that just worked fine. However, when I was doing some other things, I noticed some side effects, and in this post, we look at those side effects and how to solve them.

Click through to learn more.

Comments closed

Troubleshooting an Error: Insufficient System Memory

Published 2020-12-10 by Kevin Feasel

Lee Markup walks us through an issue:

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

Read on to see how Lee was able to find and correct the issue without actually being able to start up SQL Server.

Comments closed

Msg 7390: The Requested Operation Could Not Be Performed

Published 2020-11-20 by Kevin Feasel

Jack Vamvas fixes a problem:

 I have a SQL Server Linked Server configured , pointing to an ODBC – accessing a MongoDB driver . The test connections all work OK – and no problems running an OPENQUERY select statement using the Linked Server. 

But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table – I get an error message:

Msg 7390, Level 16, State 2, Line 6
The requested operation could not be performed because OLE DB provider “MSDASQL” for linked server “my_linked_server” does
not support the required transaction interface.

Read on to see what the problem is and how you can solve it.

Comments closed