sp_Blitz fails with collation error

Question

SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: )

Installed sp_Blitz from Brent Ozar, stored procedure creates successfully.

Version info from the code: SELECT @Version = '7.93', @VersionDate = '20200217';

The instance has the 4 system DBs with collation SQL_Latin1_General_CP1_CI_AS and 2 user databases with collation SQL_Latin1_General_CP1_CS_AS

When I try to run sp_Blitz I get this:

Msg 468, Level 16, State 9, Procedure sp_Blitz, Line 896 [Batch Start Line 0] Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Appreciate any assistance in troubleshooting (I'm mainly an Oracle guy who's been handed a SQL Server for review and remembered this as a thing that might be a good start for obvious things to start with).

Answer 1

Adding the resolution from the comments: the stored procedure was created in a case-sensitive user database, and it was trying to copy things into a case-sensitive TempDB.

After removing sp_Blitz from the case-sensitive user database, and installing it in master (which would then reflect the system database collations), it worked fine.

Answer 2

Not to dispute the vendor's recommended usage, but as far as I can tell, under "typical" conditions, sp_Blitz works just fine when installed in a user DB, even one that has a different collation than the instance. What would not be "typical", such that it would get a collation mismatch error? Using a contained database (i.e. "CONTAINMENT = PARTIAL"). The error occurs because in a contained DB, the default collation for [tempdb] user data (as opposed to [tempdb] meta data) is actually the contained DB's default collation (similar to how table variables always work).

Under "typical" conditions, the many queries in sp_Blitz that contain the following WHERE predicate (the d table alias is for sys.databases):

                    AND d.name NOT IN ( SELECT DISTINCT
                                              DatabaseName
                                        FROM  #SkipChecks
                                        WHERE CheckID IS NULL OR CheckID = 1)

work just fine, even if this stored procedure was created in a database using a default collation different than the instance-level collation, because sys.databases.name uses the instance-level collation, and #SkipChecks.DatabaseName (since there was no COLLATE clause in the CREATE TABLE statement), uses the default collation of [tempdb], which 99.999% of the time is also the instance-level collation.

The only way to reproduce the error that the O.P. was receiving is to execute the following:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

ALTER DATABASE [{user_db_containing_spBlitz}] SET CONTAINMENT = PARTIAL;

Assuming that the user DB has a different collation than the instance (and hence different from [tempdb]), then you will get the following error upon executing sp_Blitz :

Msg 468, Level 16, State 9, Procedure dbo.sp_Blitz, Line XXXXX [Batch Start Line YYYYY]
Cannot resolve the collation conflict between "{contained_db_default_collation}" and "{instance_collation}" in the equal to operation.

Again, if the vendor (i.e. Brent) says that this stored procedure is intended to be installed in [master], then probably best to follow the recommendation. I'm just explaining what was actually happening since it's non-obvious behavior.

ALSO, this is yet another case that would really benefit from a special collation name that would be dynamically replaced with whatever the instance-level collation is. So far there is DATABASE_DEFAULT that is the database's collation, no matter what that collation is, there is CATALOG_DEFAULT which is intended to help with contained DBs (though it would not help in this case), but there is nothing that represents the instance-level. General-purpose scripts such as sp_Blitz, Ola Hallengren's DB Maintenance scripts, etc that are intended to operate on the entire instance and are not coded to a specific system, will inevitably run into this problem, and will have to make unnecessary concessions in order to get it to work. So, please vote for the following suggestion of mine to fix this situation:

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation

If the temp table could be created with [DatabaseName] sysname COLLATE INSTANCE_DEFAULT, then this code would always work, no matter what DB is was placed in, whether contained or not, etc. It would just work.