A Fine Slice Of SQL Server
Gilbert Quevauvilliers wants to see what that error was again:
I have been recently using the Power BI PowerShell modules to get some information from the Power BI APIs to store the data.
I ran into an issue where something was not working and I found a great cmdlet which allowed me to view the Power BI errors that I had been querying via PowerShell.
Read on for more details and a demonstration.
Comments closedDavid 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 closedEd 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 closedJonathan 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 closedJose 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 closed