Workpoint Error 'Could not find stored procedure' during IDM Startup

Products

• CA Identity Suite

Issue/Introduction

When starting a new CA Identity Management (IDM) environment, the following error appears in the logs:

text

 

ERROR [com.workpoint.services.impl.MonitorServiceImpl] An error occurred while attempting to query the Action Queue Monitor for dsn WPDScom.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'SPWP_ASSIGN_SCRIPT_MON_ROWS'.

Users may also see errors related to NVARCHAR2 data types when attempting to recreate the database:

text

 

com.microsoft.sqlserver.jdbc.SQLServerException: Column, parameter, or variable #2: Cannot find data type NVARCHAR2.

 

Environment

Identity Suite, Identity Governance, Identity Manager.

14.5.x, 15.x

Cause

The error is typically caused by a configuration mismatch in the Workpoint property files where the database provider is incorrectly set to oracle despite using MSSQL Server. This leads to the system attempting to call Oracle-specific stored procedures or use Oracle-specific data types (like NVARCHAR2) on an MSSQL database.

Resolution

To resolve this issue, ensure the Workpoint configuration correctly identifies the database provider as SQL Server.

1. Locate the workpoint-server.properties file in your IDM configuration directory.
2. Verify the workpoint.db.provider property. Change it from oracle to mssql (or the appropriate value for SQL Server):
   properties

    

   workpoint.db.provider=mssql

3. To ensure all database objects are correctly created, set the following property to true temporarily:
   properties

    

   auto.db.create.or.upgrade=true

4. Restart the Application Server nodes.

Additional Diagnostics

• Collation: Confirm that the MSSQL Database collation is set correctly according to the Workpoint Adapter requirements.
• Permissions: Ensure the Database User has EXECUTE permissions on the Workpoint tables and the authority to create stored procedures.