The error is actually being caused by the line following the line you have highlighted in your screenshot (i.e. not the line starting with
EXEC master.dbo.xp_instance_regread... )
This error is caused by a collation mismatch between
tempdb. The reason that
tempdb is even involved here is due to
#syscollector_upload_package being a temporary stored procedure.
The following test will reproduce the error:
CREATE DATABASE [__TempCollationErrorTest] COLLATE SQL_EBCDIC278_CP1_CS_AS;
ALTER DATABASE [__TempCollationErrorTest] SET RECOVERY SIMPLE;
CREATE PROCEDURE #ThisShouldError
DECLARE @installpath nvarchar(2048) = N's';
IF @installpath = 'b' PRINT 4;
Executing the code shown above will result in the following error:
Msg 468, Level 16, State 9, Procedure #ThisShouldError, Line XXXXX [Batch Start Line YYYYY]
Cannot resolve the collation conflict between "SQL_EBCDIC278_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
DROP DATABASE [__TempCollationErrorTest];
I'm guessing that when this instance was created (using
Latin1_General_CI_AS for the instance-level collation), someone restored
[msdb] from an instance that used
SQL_Latin1_General_CP1_CI_AS as its instance-level collation, hence
[msdb] having that collation.
Of course, a far less likely yet still technically possible scenario is this instance being created using
SQL_Latin1_General_CP1_CI_AS as its instance-level collation, and someone (for whatever reason) restored the
[model] database from an instance that used
Latin1_General_CI_AS for the instance-level collation, hence
[tempdb] having that collation.
You can check to see where things starting going wrong by executing the following query, focusing on the first two columns —
SELECT DATABASEPROPERTYEX(N'tempdb', 'collation') AS [tempdb],
DATABASEPROPERTYEX(N'msdb', 'collation') AS [msdb],
DATABASEPROPERTYEX(N'model', 'collation') AS [model],
DATABASEPROPERTYEX(N'master', 'collation') AS [master],
SERVERPROPERTY('collation') AS [instance];
I'm not sure how much data you have in that
[msdb] database (assuming that it is the one with the collation that doesn't master the others), but you will need to change its collation to match the others. This is easiest if this is a new install, but perhaps that is not the case with this being a system that is being patched.
Maybe you could export the data from
[msdb] to a file, restore
[msdb] from an instance created using
Latin1_General_CI_AS for the instance-level collation, then reload the data into
[msdb]. For the data import/export, perhaps look into using the SqlPackage.exe utility that comes with SQL Server Data Tools (SSDT). For getting a new
[msdb], you might need to create a new instance of SQL Server (a throw-away, just to get a clean
[msdb]), in which case you might be able to use SQL Server Express, or maybe best to use the install media used to create the current instance.
If that does not seem possible, then perhaps consider updating the collation of all databases, including the system DBs and even the instance itself, to
Latin1_General_100_CI_AS_SC using the undocumented approach that I have described in detail in the following post:
Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
I am suggesting
Latin1_General_100_CI_AS_SC as it is:
- a safe upgrade given it's the same code page (Windows 1252) and sensitivities (case-insensitive, accent-sensitive)
- much improved over the base
Latin1_General_* collations (i.e. those without
_100_ in their name)
- includes support (in terms of the built-in functions) for supplementary characters
Latin1_General_CI_AS is definitely much better than the US default of
SQL_Latin1_General_CP1_CI_AS, but anyone using SQL Server 2008 or newer really should be using the version 100 (or newer) collations.
I recommend this due to assuming that the current instance level collation is
Latin1_General_CI_AS, and the
sqlservr -q approach makes no changes if attempting to change the collation to the current instance-level collation. So, a different collation would be necessary to use anyway in order to get the operation to process the databases, and the safest collation to use would be something very similar, such as the one I suggested, or even just
Latin1_General_CI_AS_KS. If you must maintain using
Latin1_General_CI_AS, then you can use
Latin1_General_CI_AS_KS as a temporary collation to get all DBs to match, then run the operation a second time to make everything the desired
Latin1_General_CI_AS. In this case, assuming you don't want to touch any user databases, those can be detached prior to running
sqlservr -q, and then re-attach them once the system databases are all using the desired collation. This will keep the user databases from being affected by the operation (which is the safest approach if their collation ultimately won't be changing).