collation issue when upgrading SDM

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

The following error appears in the install_mdb.log when upgrading SDM:

DEBUG - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the UNION operation.
INFO - MDBTools_0308I - Installation of last object failed, requeuing it to the end of the list.
INFO - MDBTools_0305E - Error processing view 'View_Audit_Status.xml'; details follow:
ERROR - com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the UNION operation.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the UNION operation.

Collation in audit_log table is SQL_Latin1_General_CP1_CI_AS but there are fields with SQL_Latin1_General_CP1_CI_AI collation.

Refer to the following link to verify the collation settings of the MDB: View Collation Information

Environment

SDM 17.x

Database Engine: SQL Server 

Cause

Collation is a set of rules for comparing characters in a character set. It determines how string comparison is performed, including rules for case sensitivity and accent sensitivity. During the installation or upgrade scenario, the data of the audit_log table is not touched/modified by the installer. Customization of the collation in some fields of the audit_log are causing this issue to occur.

Resolution

1. Take the backup of audit_log table

2. Update the collation settings of the columns to make sure they match the collation settings of the Database and Table. Refer to the Microsoft SQL Server Documentation to implement the changes: Set or change the database collation

3. Once the upgrade is done modify the collation settings per your requirements. 

Additional Information