1

Need urgent help on this. We have applied latest sql patches on one of the server (SQL 2014, SP3, CU4) and somehow we were not able to start the SQL due to following error.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. Error: 3417, Severity: 21, State: 3. Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.

So, i have started the server with Trace flag 902 and run the script msdb110_upgrade script and it failed with Msg 468, Level 16, State 9, Procedure #syscollector_upload_package, Line 37659 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the not equal to operation.

Can anyone please help to fix this issue? enter image description here

3
  • 2
    The msdb110_upgrade script should never throw errors & prevent startup. (However, I have seen quite a few problems with the latest CUs and this script.) I'd suggest opening a case with Microsoft.
    – AMtwo
    May 18 at 17:26
  • Is your master database (and furthermore your instance) set to the Latin1_General_CI_AS collation?
    – J.D.
    May 18 at 18:10
  • Chickoo: were you ever able to resolve this? Did my answer below help? Please provide some feedback to let us know the current status of this issue. Thanks. Jun 16 at 22:19
2

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 msdb and 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:

Setup

CREATE DATABASE [__TempCollationErrorTest] COLLATE SQL_EBCDIC278_CP1_CS_AS;
ALTER DATABASE [__TempCollationErrorTest] SET RECOVERY SIMPLE;
GO
USE [__TempCollationErrorTest];

Test

GO
CREATE PROCEDURE #ThisShouldError
AS
DECLARE @installpath nvarchar(2048) = N's';
IF @installpath = 'b' PRINT 4;
GO

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.

Cleanup

GO
USE [master];
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 — [tempdb] and [msdb]:

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:

  1. a safe upgrade given it's the same code page (Windows 1252) and sensitivities (case-insensitive, accent-sensitive)
  2. much improved over the base Latin1_General_* collations (i.e. those without _100_ in their name)
  3. includes support (in terms of the built-in functions) for supplementary characters

I mean, 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).

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.