Datamart job gives error "specified name already exists"

Document ID : KB000110932
Last Modified Date : 26/10/2018
Show Technical Document Details
Issue:
The PPM Datamart Extraction job fails with following error message:

ERROR 2018-08-13 02:31:35,451 [Dispatch Datamart Extraction : bg@ (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.

See also Knowledge Document:
kb000049533

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.