Press "Enter" to skip to content

Category: Error Handling

Ways to Debug T-SQL Scripts

Simon Frazer shares some tips:

At some point, every SQL developer or DBA will need to debug T-SQL scripts, either to verify that they behave as expected or to track down the root cause of a problem. Whether you’re building something new or investigating a production issue, debugging is an essential part of the process.

There are several techniques available for troubleshooting, and it’s important to approach this differently depending on whether you’re working in a production or non-production environment. Each environment has its own risks and constraints.

Click through for Simon’s process. I also echo Simon’s sentiments at the end regarding the SSMS debugger—I know people who are passionate about it and mourn its passing, but I was never one of those people. It was far too easy to get in trouble with it, especially in shared environments.

Leave a Comment

Error Handling in Microsoft Fabric Translytical Task Flows

Jon Vöge continues a series on write-back in Microsoft Fabric:

In my pursuit of testing out Translytical Task Flows and User Data Functions as a write-back alternative to Power Apps, I’ve come to spent a good amount of time trying to debug those features as well. Especially since they have a tendency to throw pretty non-descriptive error messages your way.

For this week’s blog post, I’ve made a small write-up of tips and tricks for troubleshooting and debugging translytical task flows, as this was something I struggled a little with myself.

Read on for several tips around better testing and error handling within these functions.

Leave a Comment

Bad Request Error Running Powershell in Azure DevOps

Koen Verbeeck wants good requests:

I needed to run a PowerShell cmdlet in an Azure Devops pipeline. The cmdlet in question was New-AzRoleAssignment, but the cmdlet itself isn’t important. What is important is that I needed to pass the object ID of a service principal to the command. Even though I was pretty sure the syntax and everything was correct, I got a “Operation returned an invalid status code ‘BadRequest’” error when the PowerShell was run (inside an Azure PowerShell task):

Read on to see how Koen diagnosed and resolved the issue.

Leave a Comment

Troubleshooting Network-Related or Instance-Specific Error

Aaron Bertrand has started a new series:

This is the first in a series of articles meant to provide practical solutions to common issues. In this post, we’ll talk about one of the most pervasive error messages out there:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Read on to see what a variety of potential solutions to this problem. I was going to joke “It’s always DNS” but Aaron actually has a section on DNS in there.

Leave a Comment

SQL Server Error Log Tips

Kevin Hill notes that the error log isn’t just for errors:

SQL Server’s Error Logs often get overlooked, until something breaks. But if you know how to use them, they can tell you what went wrong before you’re knee-deep in outage mode.

Whether you’re a sysadmin who inherited SQL or an MSP juggling multiple environments, understanding the ErrorLog is low effort, high return.

Let’s decode the basics.

Click through for examples of information you can find in the error log. Kevin mentions cycling the error log weekly. Back in my DBA days, I’d cycle them daily because I didn’t want the files to grow too large and become unwieldy. Some of this, of course, depends upon how hard people are pushing that box and how much you need to log.

Leave a Comment

Upsert Patterns and Duplicate Keys in T-SQL

Ajay Dwivedi runs into an error:

I work with developers quite a lot. There are scenarios where data is received from various sources in an application, and asynchronously pushed to database in multiple sessions/connections.

There are situations where concurrency and transaction speed is high enough to cause below UPSERT code blocks to fail with error message like Cannot insert duplicate key row in object dbo.person with unique index ‘pk_person’

Click through for one way to do things. I will note that Ajay has concerns about the MERGE operator, but Hugo Kornelis took a deep dive into all of the known problems in MERGE and found that most of them were fixed. Hugo’s post does make it clear when using MERGE is a bad idea, as there are still some situations in which it won’t work effectively, but for something like this, it would be fine.

I’ll say that I’m generally not a fan of app locking. There are specific circumstances in which it’s the best answer, but those are rare. Here, I’d rather just use a ROWLOCK table hint or change the serialization level.

Leave a Comment

The Costs of Implicit Conversion

Andy Brownsword changes things around:

Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.

Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.

Read on for Andy’s example.

Comments closed

Building a Better Trigger

Steve Jones is done with mousetraps:

This seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

Steve hit on one of the problems in trigger design, but there’s another problem that we often see. Steve’s code doesn’t have this problem, but quite often, people assume that rows update one at a time. But if you write a T-SQL UPDATE statement that affects five rows, the trigger fires once for all five. Steve correctly uses the inserted and deleted pseudo-tables to handle all changes, rather than just assuming a single row.

Comments closed