4

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).

  • Invoked like this: EXEC [dbo].[sp_Blitz] @CheckUserDatabaseObjects = 0 , @CheckProcedureCache = 0 , @OutputType = 'TABLE' , @OutputProcedureCache = 0 , @CheckProcedureCacheFilter = NULL, @CheckServerInfo = 1 Tried with CheckUserObjects set to both 0 and 1. Same error. Thanks for the quick reply @Brent – SJWales Feb 20 at 18:45
  • Hey @BrentOzar - does this make sense to you? It appears to be happening on the insert into #BlitzResults - Comments out CheckID #1, line number of the error moved. Commented out CheckID2, and it moved again. Added a couple of "print" statements and it appears to be that. I had also installed the proc into the user DB. Dropped it from there and created it in master and it ran OK. A little weird but working now :) – SJWales Feb 20 at 20:07
  • Ah, okay - yeah, it's not really supposed to be in the user databases. It's a server-level health check, so you wanna install it in master. – Brent Ozar Feb 20 at 20:10
6

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.

|improve this answer|||||
  • 1
    Hey Brent. I think you meant to say case-insensitive TempDB :-). But also, while the case-sensitivity was an issue for the O.P., the only reason that was even noticed was due to the O.P.'s DB being "contained". If it were not for that, then most likely the O.P. never would have seen a collation mismatch error. At least, I was unable to reproduce any collation error until I change my test DB to be contained. Please see my answer for details 😺 – Solomon Rutzky Feb 21 at 22:03
  • Cool, thanks sir! – Brent Ozar Feb 22 at 11:46
3

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.