Changing the Database Collation of the Upgraded Application Catalog

Generally, an Application Catalog database obtains its collation setting from the SQL Server collation setting, unless one specifies a different collation setting. Therefore, if an SQL Server is running with the collation setting of Latin1_General_CI_AS, then any database created on this SQL Server will have the collation setting of Latin1_General_CI_AS by default.

However, AdminStudio 2023 requires the database collation to be SQL_Latin1_General_CP1_CI_AS. If a new Application Catalog is created using AdminStudio 2023, that Application Catalog database is created with the correct collation setting, irrespective of the SQL Server collation setting. However, if you upgrade an Application Catalog database from a previous version to the AdminStudio 2023 schema using the Upgrade Wizard or using scripts, the collation setting is not changed, so you must perform the steps below to change it.

Note:In the United States, the default collation for any SQL Server is SQL_Latin1_General_CP1_CI_AS. However, in other geographies such as the United Kingdom and Europe, the default collation is set to something else. Therefore, this topic is more relevant to users located in geographies outside of the United States.

Important:If the collation setting of an AdminStudio 2023 Application Catalog is not set to the correct collation setting, you will be able to connect to it, but it will cause Application Manager to periodically crash. An error message such as the following will appear in the log file (ISCMIDE.log):

'Exception 'System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.'

To change the collation setting of an AdminStudio 2023 Application Catalog database to SQL_Latin1_General_CP1_CI_AS, perform the following steps:

To change the collation setting of an AdminStudio 2023 Application Catalog database:

1.

Download the following ZIP file and extract the files to a temporary folder:

https://docs.flexera.com/adminstudiocc/CollationChange.zip 

2.

Open SQL Management Studio and run the Pre_Collation_Change.sql file against the AdminStudio 2023 Application Catalog database.

3.

Open the Collation_Change.sql file in an editor and replace the AS_DATABASE_NAME string with the name of the Application Catalog database that you are upgrading.

4.

Run the Collation_Change.sql file against the AdminStudio 2023 Application Catalog database.

5.

Run the Post_Collation_Change.sql file against the AdminStudio 2023 Application Catalog database.

The collation setting of the AdminStudio 2023 Application Catalog database will now have been changed to SQL_Latin1_General_CP1_CI_AS.