Clarity: Datamart Job Failed - The DBMS returned an unspecified error 224 - The specified name already exists

Document ID : KB000049533
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The Datamart Extraction job failed. We are using an MSSQL Database.

The Clarity Background Log shows the following error message:

niku.njs (none:none:none) Error executing job: 5339858
com.niku.union.persistence.DBConnectionException: java.sql.SQLException:
[CA Clarity][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224.
at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:1830)
at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2356)
at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:703)
at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:611)
at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:451)
at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:222)
at com.niku.njs.listeners.PMDJob.scheduledEventFired(PMDJob.java:75)
at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:277)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:727)
at java.lang.Thread.run(Thread.java:662)

Additional information found. When executing the Datamart Extraction job stored procedure in a database tool, we see the following error:

Processing NBI_PRT_FACTS
Time: 00:00:00
Deleting datamart excluded resources from NBI_PRT_FACTS
Time: 00:00:00
Processing NBI_R_FACTS
Time: 00:00:00
Deleting datamart excluded resources from NBI_R_FACTS
Time: 00:00:00
Processing NBI_PROJECT_CURRENT_FACTS
Msg 14525, Level 16, State 1, Procedure sp_delete_job, Line 104
Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 46
The specified @name ('NBI1') already exists.
Msg 50000, Level 15, State 15, Procedure CMN_RAISERROR_SP, Line 14
-20999_CMN_DB_JOB_ADD_SP Adding job - error 0 return code 1
Msg 14525, Level 16, State 1, Procedure sp_delete_job, Line 104
Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 46
The specified @name ('NBI2') already exists.
Msg 50000, Level 15, State 15, Procedure CMN_RAISERROR_SP, Line 14
-20999_CMN_DB_JOB_ADD_SP Adding job - error 0 return code 1
Query was cancelled by user.

Solution:

The error message 'Only members of sysadmin role are allowed to update or delete jobs owned by a different login.' indicates that the Datamart Extraction job was executed in the database tool by a user not authorized to execute the job. The Datamart Extraction job stored procedure should NOT be executed directly in a database tool unless it is done at the direction of CA Technical Support and if other instances of the job are running from the application.

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.

  1. Please check to ensure there are no Datamart Extraction job scheduled, waiting or processing within the Clarity application

  2. Delete all scheduled instances of the 'Datamart Extraction' job

  3. Have your DBA check to see if there are any SPIDS showing processing for the NBI1, NBI2, etc (If so, kill these SPIDS)

  4. Then run an 'immediate' Datamart Extraction job from Clarity

Keywords: CLARITYKB, dme, mssql.