Collation error on Identity Manager workpoint (WPDS) database

Products

• CA Identity Suite
• CA Identity Manager

Issue/Introduction

When restarting the Identity Manager we have the following error in the Identity Manager log

ERROR [com.workpoint.services.impl.GenericServiceImpl] (Query_jobq#JobQ1#WPDS) Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.
ERROR [com.workpoint.services.impl.MonitorServiceImpl] (Query_alertq#AlertQ1#WPDS) An error occurred while attempting to query the Alert Queue Monitor for dsn WPDS com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

 

The approval process does not work correctly.

Environment

Identity Manager 14.x

Cause

The collation was not configured correctly while creating the Workpoint database

The correct collation is "SQL_Latin1_General_CP1_CI_AS" as documented in the link below

https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/identity-manager/14-5/installing/installation-prerequisites/central-databases.html

Resolution

You can try to change the WPDS collation as described in the Microsoft document below:

https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver15

 

If it does not work, try the following steps:

- Stop Identity Manager services

- Create a new Identity Manager Workflow database with the correct collation "SQL_Latin1_General_CP1_CI_AS" 

- From the Virtual Appliance dashboard, edit the Database configuration and change the Identity Manager Workflow to the new database

- Save the database information

- Start Identity Manager services.

During the Identity Manager startup, the tables will be created.

Additional Information

If you have a custom workflow, export them using the Workpoint Archive tool and import them to the new workflow database.

https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/identity-manager/14-5/configuring/production-environments/update-identity-manager-in-a-production-environment/migrate-workflow-process-definitions.html