A Fine Slice Of SQL Server
Chris Webb troubleshoots a problem:
If you’re working in Power BI Desktop you may sometimes find that your visuals error with the message “Couldn’t load the data for this visual. The operation was cancelled because of locking conflicts”:
Read on to see what causes this problem and what you can do to avoid it.
Comments closedSteve Stedman fights with collations:
One possible cause of the “Cannot resolve the collation conflict” error message is that your database collation doesn’t match the TempDB Collation.
[…]
Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Read on to see an example of how you can cause this problem and what you can do about it. I agree with Steve’s general advice to keep collations the same across an instance if you can.
Comments closedGilbert 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 closed