cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

AlterDatabaseCollation.cmd returns errors about collation conflict

AlterDatabaseCollation.cmd returns errors about collation conflict

This is just a quick post about a potential error for which I found no directly pertinent information.

If you're upgrading from Windchill 10.x to Windchill 11.x, and you're using SQL Server for the database, you need to upgrade the database to SQL Server 2012 or newer.  One of the upgrade steps is to run the AlterDatabaseCollation.cmd batch file to change the collation from the old supported version to the new supported version.

If that batch file errors out and reports something similar to "Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 1", then you probably made the same mistake I made, changing the database containment type too early in the process.

The AlterDatabaseCollation.cmd batch file needs to be run against a database that does not use partial containment because the scripts, as written, cannot account for the different collations that come into play on SQL Server when a database uses partial containment.  Either change the database back to Containment Type: None, as it was in the old SQL Server, or re-import the old database backup, and restore the login for the database user using the old, un-contained login method.  The AlterDatabaseCollation.cmd batch file should now run without the collation conflict errors, and you can follow the remaining steps in the collation change instructions.

Once the collation is changed, you can then change the database containment to partial and re-create the database user as a contained user (which is the default database authentication method for Windchill 11.0).

Hopefully, I put enough key words and tags in this post that it can be found by anyone who runs into the same nebulous error in the future.