Datamart job gives error "specified name already exists"

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The PPM Datamart Extraction job fails with following error message:

ERROR 2018-08-13 02:31:35,451 [Dispatch Datamart Extraction : [email protected] (tenant=clarity)] niku.njs (clarity:admin:7433941__F3107E7B-9F63-4C98-8711-D2EF17C31FF4:Datamart Extraction) Error executing job: 5105005 
com.niku.union.persistence.PersistenceException: 
SQL error code: 14261 
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The specified @name ('NBI2') already exists. 

Cause

The error messages 'The specified @name already exists' for the Datamart Extraction job is caused by SPIDS still processing a previous execution of the job.

In the specific case here, the error may also show up when having set up the following environment:

- MSSQLServer with two CA PPM schemas, installed on a single server, for example for CA PPM Production and Development.
- Starting the Datamart Extraction Job at the same time for both CA PPM environments.

Resolution

• Due to having two schemas on the same MSSqlserver, the Database Engine may execute Stored Procedures with the same name against the wrong schema, causing the "name already exist" error message
• This is only seen to occur with MSSQLServer, not with Oracle.

The solution is:

• Do not execute the Datamart Extraction Job at the same time on both CA PPM environments.