Press "Enter" to skip to content

Category: Error Handling

TRY-CATCH in T-SQL

Published 2022-05-16 by Kevin Feasel

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

Azure SQL DB ARM Template Conflicts with Azure AD Administration

Published 2022-05-06 by Kevin Feasel

Joao Antunes points out a potential timing issue around combining Azure Active Directory administration with Azure SQL Database ARM templates:

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

Empty Catch Blocks in T-SQL

Published 2022-05-03 by Kevin Feasel

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

Diagnosing an Always Encrypted Decryption Issue

Published 2022-04-06 by Kevin Feasel

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

DiskChangeFileSize Error on Backups

Published 2022-04-05 by Kevin Feasel

Greg 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

Cross-Database Graph Query Problems

Published 2022-03-21 by Kevin Feasel

Louis Davidson receives a nastygram from SQL Server’s graph functionality:

Just understand that if you need any of the graphDb underlying data structures, you will need to find their actual physical name and use it. I would definitely suggest never accessing these columns via any method other than the pseudocolmns for production code (because you have no way to predict the column names from dev to prod (you cannot specify the names when creating a table), but this following code does work:

Click through to see the issue and Louis’s workarounds.

Comments closed

Temporal Tables and Azure DevOps Deployments

Published 2022-01-27 by Kevin Feasel

Rayis Imayev notes a problem with Azure DevOps deployments:

Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.

Read on to see what causes this problem and what we can do to work around it.

Comments closed

Bidirectional Transactional Replication and Server Names

Published 2022-01-20 by Kevin Feasel

Mousa Janini points out a requirement of bidirectional transactional replication:

The steps to create a Bi-directional replication is simple, and similar to the steps for configuring transnational replication with extra step to enable the @loopback_detection parameter of sp_addsubscription to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.

The most common issue for the Bi-directional replication is when the loop back detection is not working as expected; which results in data conflicts and Primary Key Violations.

Read on to see what is the cause of this problem and what you can do to solve it.

Comments closed