Press "Enter" to skip to content

Category: Error Handling

SQL Alerts on Potential Attacks

Published 2023-03-31 by Kevin Feasel

David Fowler keeps his head on a swivel:

But let’s be honest, the above attacks are just going to be opportunistic hackers probably using an automated tool to hunt out open servers and try to exploit them, a bit like the guy walking down the road and trying all the car doors in the hope that someone will have left their unlocked. It someone’s specifically targeting your servers, the chances are that they’ll be a bit more sophisticated.

This is where things get a little more troublesome and where SQL doesn’t really give us any tools to help.

But there are warning signs that we can look out for and these can be certain error messages in the logs. There are three in particular that I see as red flags that something amiss may be going on.

Click through for those three warning signs and how you an create SQL Server alerts upon logging these messages. The errors aren’t guaranteed to be an attacker, though in reading them, I’d expect a high signal to noise ratio on them.

Comments closed

Tips for Debugging DAX Code

Published 2023-03-14 by Kevin Feasel

Ed Hansberry has no bugs, but just in case:

When trying to get your DAX measures to work correctly, there are a number of tools you can use to debug, which I will briefly mention, but not go into depth on. Instead, this post is about how to think about debugging your code. I am focusing on DAX here, but many of the methods would apply to any type of code.

Read on for a series of tips around built-in capabilities, process, and the power of conversation.

Comments closed

The Importance of Trace Flag 460

Published 2023-03-10 by Kevin Feasel

Jonathan Kehayias helps me understand that Trace Flag 460 is one of the best trace flags ever:

For most people that are reading this post, I’d venture to guess that you have no idea what Trace Flag 460 is or when you would use it. Well first off, let me tell you it’s a fully documented and supported trace flag, and it’s totally safe. In fact, on SQL Server 2019 and higher it is the default behavior when you create a new database. What does it do? It makes troubleshooting string or binary truncation issues easier by changing the error message that is returned from message ID 8152 and replaces it instead with message ID 2628. The trace flag is also available in SQL Server 2016 SP2 CU6+ and SQL Server 2017 CU12.

Read on to learn more about it and to remove a potential bit of confusion in the documentation.

Comments closed

Copy-Only Backup and Next Automatic Backup

Published 2023-03-02 by Kevin Feasel

Jose Manuel Jurado Diaz diagnoses an error:

Today, we worked on a service request that our customer got the following error message: BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation [SQLSTATE 42000] (Error 41937) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013), running a manual backup.

Click through to learn when you might see this error and what you can do about it.

Comments closed

The Most Common SQL Server Engine Errors Generating Support Tickets

Published 2023-03-01 by Kevin Feasel

Joseph Pilov collects a list:

About 6 months ago we decided to look at what SQL Server engine error messages are most commonly generating support cases to Microsoft. The end goal was to update the documentation for those error messages to allow our customers to find answers for themselves before they have to call Microsoft for technical assistance.

The task, as we suspected from previous experience, was not easy because we had to mine cases for error numbers and a relatively small number of support cases get reported with error messages when they are opened. Please report full error messages when you open support cases with Microsoft – it would help us get you answers faster. Still, we were able to find the trends even from the small percentages because were looking for the top 20 or so most common errors, based on case count, and we needed relative information – which error is reported more than another.

Click through for the list. A benefit from going through this exercise is that Microsoft has provided more information on each of those error IDs, hopefully making it easier for people to diagnose and resolve problems without needing to reach out to support.

Comments closed

Working with Postgres Extensions in Azure Cosmos DB

Published 2023-02-17 by Kevin Feasel

Sarah Dutkiewicz runs into an issue:

Problem: I installed PostGIS on my single-node cluster without issues. However, I scaled my cluster to 2 nodes afterwards. When I ran the query that uses ST_X and ST_Y from PostGIS, I got the following error:

ERROR: type "public.geometry" does not exist
CONTEXT: while executing command on private-w0.azure-cosmos-db-global-ug-demo.postgres.database.azure.com:5432

When I read the CONTEXT message, I realized by the w# reference that the worker nodes didn’t have PostGIS installed. When you scale the nodes – at least in this case, it doesn’t enable the extensions over there.

Read on to see how Sarah was able to resolve this issue.

Comments closed

Error Handling with OPENROWSET

Published 2023-02-14 by Kevin Feasel

Deborah Melkin handles missing servers with aplomb:

OPENROWSET is a functionality that allows you to access data sources outside your current server. This could be reading from an Excel file or calling another SQL Server instance. You’re able to treat that other data source as record set, or derived table, and work with the rows returned as you would a local table. One reason you may want to do this is that you need to use a stored procedures to query data from other servers and bring the data together, effectively creating an ELT (Extract – Load – Transform) process without having to use SSIS or Azure Data Factory (ADF).

Read on to see how OPENROWSET() works, what happens if you try to access a remote server which doesn’t exist (or times out), and how you can capture that error message in a CATCH block—something that is not possible to do by default.

Comments closed

Unsupported Characters in Azure SQL DB Database Names

Published 2023-02-07 by Kevin Feasel

Nithya Bondalapati notes a discrepancy:

ARM (Azure Resource Manager) API does not support specific T-SQL resource naming formats and that’s why when Databases are created using T-SQL/SSMS, unsupported characters could get allowed into the names.
Azure Portal UI does not have this issue, because when you use Portal UI for creating a Database, the creation goes through ARM, and hence when unsupported characters are entered in the Name field, it immediately throws error, as shown in the below image.

None of the restrictions or onerous, but Nithya explains what actions fail or don’t quite work right if you have a database with special characters or ending in a period or space.

Comments closed

Data Types and CONCAT_WS

Published 2023-01-31 by Kevin Feasel

Koen Verbeeck hits a concatenation issue:

I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very long, for example if a dimension has a lot of columns. When debugging, I noticed a couple of statements failing with various errors. Turns out, they were truncated after 4000 characters. What was going on?

Read on to see what happened.

Comments closed