A Fine Slice Of SQL Server
Gilbert Quevauvilliers diagnoses an error:
I have been doing a lot of work recently using Power BI Premium Per User and recently I got the following error below when trying to update my fact table in Power Query.
“An unexpected error occurred (file ‘tmsavepoint.cpp’, line 1503, function ‘TMSavePoint::GetProxyImpl’)”
Read on to understand what this error means and how you can fix it. Do be sure to look out for the very important warning about 2/3 of the way in.
Comments closedAndrew Pruski troubleshoots a problem:
I was adding databases to an availability group (SQL Server 2017 CU20 instance) the other day and one database failed to automatically seed to the secondary.
When I looked in the SQL Server error log I saw this error message: –
BackupIoRequest::ReportIoError: write failure on backup device ‘{GUID}’. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Read on to see how Andrew solved the problem.
Comments closedSolomon Rutzky has an apropos post for Friday the 13th:
In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the
PARSEONLYsession setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than
PARSEONLYas it only works on individual batches, and it only works in some environments.
Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.
Chad Callihan plays hot potato:
When it comes to error handling or troubleshooting a long stored procedure, RAISERROR is an easy statement to use that gets the job done. Way back in SQL Server 2012, Microsoft wanted to replace RAISERROR with the new (arguably less convenient) THROW statement. I thought it would be worth looking at an example using THROW and what it takes to have custom messaging with a parameter.
THROW can be a little less convenient, but conceptually speaking, I do think it’s better than the alternative. The part which makes it tricky in practice is that so many types of T-SQL errors are non-catchable, so as a developer, you have to keep on your toes about it.
Andy Leonard troubleshoots an issue:
If I had a nickel for every time I’ve encountered “Cannot load script for execution,” I’d have a lot of nickels.
Save your nickels and read Andy’s post.
Comments closedJack Vamvas answers a question:
Question: I’m attempting to create a backup of a SQL Server read only database , but getting an error message – BACKUP LOG cannot be performed because there is no current database backup
Why is this happening and are there any workarounds?
Read on to understand the problem, as well as the red herring in the question.
Comments closedSolomon Rutzky tries Betteridge’s Law of Headlines:
Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that
sysnamehad to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior ofsysnamename resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):
Read on for the results of Solomon’s archaeological expedition.
Comments closedHello! I’m very slowly working on writing a zine about debugging, so I asked on Twitter the other day:
If you’ve run into a bug where it felt “impossible” to understand what was happening – what made it feel that way?
Of course, bugs always happen for logical reasons, but I’ve definitely run into bugs that felt like they might be impossible for me to understand (until I figured them out!)
I got about 400 responses, which I’ll try to summarize here. I’m not going to talk about how to deal with these various kinds of “impossible” bugs in this post, I’ll just try to classify them.
Click through for the major categories, as well as explanations and sub-categories. I think an interesting follow-up to this is to ask why we find ourselves in situations where we get these sorts of bugs and what (if anything) we can do to minimize or eliminate the likelihood of their appearance.
Comments closedDavid Fowler takes us through a mental exercise:
Well, there is something else that I’ve seen have a habit of filling TempDB. If you’re using Read Committed Snapshot or RCSI then you’ll also have a version store in your database. I’m not going to go into the details of exactly how version store works, there is plenty of documentation on it out there if you’re interested (perhaps I’ll write a post on it sometime).
The key bit that you need to know is that although SQL will keep version store trimmed down and only keep the rows that are needed, it can only clear rows that are older than the oldest transaction. This is because SQL has no way of knowing what rows are going to be needed by the transaction. Do you see a potential issue here?
Read on for enlightenment.
Comments closedManoj Pandey explains an error message in Spark SQL:
UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge, when multiple source rows match on the same target row, the result may be ambiguous as it is unclear which source row should be used to update or delete the matching target row. You can preprocess the source table to eliminate the possibility of multiple matches. Please refer to https://docs.microsoft.com/azure/databricks/delta/delta-update#upsert-into-a-table-using-merge
The above error says that while doing MERGE operation on the Target table there shouldn’t be any duplicates in the Source table. This check is applied implicitly by the SQL engine to avoid unnecessary updates and avoid inconsistent data.
Read on for a reproduction and what you can do to resolve the issue.
Comments closed