Install error: Column is not of same collation as referencing column

Products

• CA Service Catalog

Issue/Introduction

The CA Service Catalog install fails at the MDB step and the following errors are seen in the %temp%\install\MDB.log:

INFO - MDBTools_0305E - Error processing foreign key '$usm_a_r0000203100000000.xml'; details follow:

ERROR - com.microsoft.sqlserver.jdbc.SQLServerException: Column 'ca_time_zone.time_zone_code' is not of same collation as referencing column 'usm_account.time_zone_code' in foreign key '$usm_a_r0000203100000000'.
com.microsoft.sqlserver.jdbc.SQLServerException: Column 'ca_time_zone.time_zone_code' is not of same collation as referencing column 'usm_account.time_zone_code' in foreign key '$usm_a_r0000203100000000'.

MDBTools_0304E - Possible dependency error processing permission 'usm_sp_system_change_details_multi_param[usmgroup].xml'; error details follow:
ERROR - Cannot find the object 'usm_sp_system_change_details_multi_param', because it does not exist or you do not have permission.
ERROR - MDBTools_0307E - SQL text:
ERROR -    GRANT EXECUTE ON [dbo].[usm_sp_system_change_details_multi_param] TO [usmgroup]  WITH GRANT OPTION

Environment

CA Service Catalog 17.x

SQL Server Database server

Cause

SQL Server collation mismatch

Resolution

Due to the collation conflict between MDB common tables (i.e. ca_time_zone, ca_currency, ca_locale) and CA Service Catalog tables, the installation fails at the MDB step.

Although the correct collation value is SQL_Latin1_General_CP1_CS_AS, needed to explicitly change the column collation value to SQL_Latin1_General_CP1_CI_AS for any CA Service Catalog tables that involve the following fields:

• time_zone_code
• currency_type_codel
• locale_code

Following database queries can be used as a template to change the collation settings:

ALTER TABLE usm_task ALTER COLUMN time_zone_code varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER TABLE usm_billing_account ALTER COLUMN currency_type_code varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER TABLE usm_contact_extension ALTER COLUMN locale_code varchar(4) COLLATE SQL_Latin1_General_CP1_CI_

IMPORTANT: Take a backup of the MDB database prior to making any changes

After addressing the collation mismatch, the CA Service Catalog install will proceed successfully.