sp_blitz fails with: Msg 468, Level 16, State 9 Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN"

Question

Has anyone had an issue with running sp_blitz on an instance using the Latin1_BIN collation? I created the stored procedure without error, but when I ran it I got the following error message:

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

I then created a separate database with the Latin1_General_CPI_CI_AS collation, create the sp_blitz procedure, and this threw out more errors when executed. Probably due to the joins to the system object.

Is there an easy way to fix this or an updated script, without me having to troubleshoot and rewrite every line of code?

sp_blitz info

@Version = '8.03', @VersionDate = '20210420'

collation query results

InstanceCollation     SystemDbCollation     TempDbCollation
Latin1_General_BIN    Latin1_General_BIN    SQL_Latin1_General_CP1_CI_AS

Answer 1

The initial problem is that yes, there are several places in sp_blitz that compare a string column: database name. The comparison is usually (I didn't check the entire proc) between the system catalog view, sys.databases, and the temporary table, #SkipChecks in a NOT IN predicate:

FROM    master.sys.databases d
...
WHERE   ...
        AND d.name NOT IN ( SELECT DISTINCT
                            DatabaseName
                            FROM  #SkipChecks
...

Part 1

So, the immediate problem is that:

1. The collation of the name column in sys.databases is Latin1_General_BIN, which correctly reflects your instance/server -level collation, but the collation of the DatabaseName column in SkipChecks is SQL_Latin1_General_CP1_CI_AS due to this being a temporary table and by default, the collation of string columns is taken from the default collation of tempdb, which on your system is SQL_Latin1_General_CP1_CI_AS.

   AND

2. The collation of the predicate is not being forced via the COLLATE option.

This is not a problem/error most people get because tempdb nearly always has the same collation as the instance/server and the other system databases (i.e. master, msdb, and model.

Part 2

That brings us to the root cause of the problem: under normal conditions, the collation of tempdb should nearly always be the same as the instance/server -level collation, as well as the other three system databases. This is handled during installation. (And, I emphasized "nearly" due to contained databases using the database's default collation for string columns in temp tables.)

Somehow your tempdb collation is different than the instance/server -level collation, as well as the master and (I assume) msdb databases. I suspect that the model database was restored from an instance configured to use SQL_Latin1_General_CP1_CI_AS (since tempdb is rebuilt from model every the time instance starts up).

An easy way to confirm this is to use a modified version of the query I had you execute to get the server and tempdb collations (this time we will get the collation for model, perhaps something we should have done the first time around, as well as msdb just to be sure):

SELECT SERVERPROPERTY('collation') AS [InstanceCollation],
       DATABASEPROPERTYEX(N'master', 'collation') AS [MasterDbCollation],
       DATABASEPROPERTYEX(N'msdb', 'collation') AS [MSDbCollation],
       DATABASEPROPERTYEX(N'model', 'collation') AS [ModelDbCollation],
       DATABASEPROPERTYEX(N'tempdb', 'collation') AS [TempDbCollation];

Fix

Hence, to fix this:

You cannot execute ALTER DATABASE [model] COLLATE ....; , not even when running in Single User mode, since it is a system database.

You will need to restore the model database from an instance configured to use Latin1_General_BIN. If you don't have another instance already using Latin1_General_BIN, then just go through the install process (on a non-production server), select the fewest options possible, and stick with all defaults except for the collation, which should be set to Latin1_General_BIN.