A Fine Slice Of SQL Server
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 closedAbout 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 closedSarah 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_XandST_Yfrom 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:5432When 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 closedDeborah 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.
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 closedWhen using the Web.Contents function to call a web service in Power Query in either Power BI or Excel you may encounter the following error:
DataFormat.Error: Block length does not match with its complement.
Click through to see what the problem is as well as what you can do to resolve it.
Comments closedKoen 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 closedChad Callihan swears this is the right key:
The benefit of SQL Server Always Encrypted is to encrypt sensitive data in specified columns to prevent it from being seen by unauthorized users. It is a great feature, but there are some steps to consider besides the SQL setup side. What if you are one of the lucky (or unlucky) users who should have access to query that data unencrypted but get blocked by an error? Or, what if you’re setting up Always Encrypted and your application encounters issues decrypting data?
Let’s step through an example of one error you may come across that prevents authorized access.
Read on for the error and its root cause.
Comments closedChris Webb is spinning in circles:
When working in the Power Query Editor in Power BI or Excel, especially when you’re editing M code, you may run into the following error:
Expression.Error: A cyclic reference was encountered during evaluation.
What does this message mean and what causes this error? In this blog post I’ll explain.
Read on for the answers.
Comments closedDenny Cherry diagnoses a problem:
Over the weekend, I was configuring our new VMware servers. I was happily working around when all of a sudden, vCenter started showing the hated “no healthy upstream” message on the vCenter website.
Thankfully, this was not the first time I’d seen this happen, and it usually occurs randomly (at least in my experience). The solution is easier than most people would think.
Click through to learn what you should do if you see that error.
Comments closed