Database Synchronization job - constraint violation error occurred for table ca_discovered

Products

CA Client Automation - IT Client Manager CA Client Automation

Issue/Introduction

Database Synchronization job generates some error like :
Warning: A database constraint violation error occurred for table: ca_discovered_software

2025-05-24 01:08:07 Table: ca_discovered_software (15 of 19), Update records to process: 443
2025-05-24 01:08:07 Warning: A database constraint violation error occurred for table: ca_discovered_software
2025-05-24 01:08:07 Table: ca_discovered_software (15 of 19), Records updated: 0, Elapsed time: 00:00:00

In TRC_<engine_name>*.log there are errors like :

240525-01:08:07.0960975L|002056|00001a00|SystemEngi|amLog           |                    |000000|ERROR  | BulkCopySoftwareSignature: Software Def (sw_def_uuid=<uuid>) not exists on Target -> constraint violation error, skipping replicating
240525-01:08:07.0961701L|002056|00001a00|SystemEngi|amLog           |                    |000000|ERROR  | SetIntermediateStatus2: Sync: A database constraint violation error occurred for table: ca_discovered_software
240525-01:08:07.0967406L|002056|00001a00|SystemEngi|amLogAndNotify  |cAmoLog.h           |000387|ERROR  | Warning: A database constraint violation error occurred for table: ca_discovered_software

On Target mdb the table ca_software_def is empty. Following SQL query returns 0 :
select count(*) from ca_software_def

in TRC_CF_CONTENTUTILITY_*.log we could see this during importation of table bit_support :

150725-00:41:21.4446899L|015448|000021b4|ContentUti|RecImpl_Ado     |RecImpl_Ado.cpp     |000205|INFO   | CRecImpl_AdoCreate() enter - (select top 1 * from bit_support) (3 1 2)
150725-00:41:21.4459637L|015448|000021b4|ContentUti|RecImpl_Ado     |RecImpl_Ado.cpp     |000262|INFO   | CRecImpl_AdoCreate() terminated successfully
150725-00:41:21.4476472L|015448|000021b4|ContentUti|CDb             |CDb.cpp             |000765|DETAIL | Destroy Recordset
150725-00:41:21.4520609L|015448|000021b4|ContentUti|AmoSupportFuncti|SupportFunctions.cpp|000123|DETAIL | pwdecrypt ret1
150725-00:41:21.9813203L|015448|000021b4|ContentUti|CDb             |CDb.cpp             |000374|DETAIL | Execute SQL command
150725-00:41:21.9815566L|015448|000021b4|ContentUti|CDb             |DbImpl_Ado.cpp      |000649|INFO   | CDb_Ado::ExecSQL() enter - update bit_support set name=tmp.name,creation_user=tmp.creation_user,creation_date=tmp.creation_date from conttmp_bit_support as tmp where  bit_support.bit_support_id=tmp.bit_support_i

Correct log should be :

120825-10:56:46.3210074L|006260|000031ec|ContentUti|RecImpl_Ado     |RecImpl_Ado.cpp     |000205|INFO   | CRecImpl_AdoCreate() enter - (select top 1 * from bit_support) (3 1 2)
120825-10:56:46.3215789L|006260|000031ec|ContentUti|RecImpl_Ado     |RecImpl_Ado.cpp     |000262|INFO   | CRecImpl_AdoCreate() terminated successfully
120825-10:56:46.3222113L|006260|000031ec|ContentUti|CDb             |CDb.cpp             |000765|DETAIL | Destroy Recordset
120825-10:56:46.3244506L|006260|000031ec|ContentUti|AmoSupportFuncti|SupportFunctions.cpp|000123|DETAIL | pwdecrypt ret1
120825-10:56:47.8971102L|006260|000031ec|ContentUti|CheckBulkTables |                    |000000|INFO   | bit_support loaded to temporary table successfully
120825-10:56:47.8972852L|006260|000031ec|ContentUti|CDb             |CDb.cpp             |000374|DETAIL | Execute SQL command
120825-10:56:47.8973277L|006260|000031ec|ContentUti|CDb             |DbImpl_Ado.cpp      |000649|INFO   | CDb_Ado::ExecSQL() enter - update bit_support set name=tmp.name,creation_user=tmp.creation_user,creation_date=tmp.creation_date from conttmp_bit_support as tmp where  bit_support.bit_support_id=tmp.bit_support_id

Line "bit_support loaded to temporary table successfully" is missing indicating that import using bcp.exe command is failing.

On target mdb following SQL Query

select * from sys.schemas where name like 'ca_itrm%'

does not return the schema ca_itrm indicating that ca_itrm database schema is missing

Environment

Client Automation - All Versions

Cause

Database schema ca_itrm is missing on target mdb

Resolution

On target Microsoft SQL Server, open Microsoft SQL Server Management Studio and go under Security/Logins. Open the properties of login ca_itrm.
Click on Server Roles and check that sysadmin is not checked. Uncheck sysadmin role if it is checked.
On target SQL Server execute this to create the ca_itrm schema :

use mdb
GO
CREATE SCHEMA [ca_itrm] AUTHORIZATION [ca_itrm]
GO
ALTER AUTHORIZATION ON SCHEMA::[ca_itrm] TO [ca_itrm_group]
GO
ALTER ROLE [ca_itrm_group] ADD MEMBER [ca_itrm]
GO
ALTER USER ca_itrm WITH DEFAULT_SCHEMA = ca_itrm
GO
On source SQL Server execute following SQL Query to find the DOMAIN uuid of target mdb :

use mdb
select '0x'+SUBSTRING(CAST(jocont AS NVARCHAR(MAX)), CHARINDEX('database_uuid',CAST(jocont AS NVARCHAR(MAX)))+14,32) from ncjobcfg
where jotype=36 and joname='SYNC DB'

Replace SYNC DB by the name of the synchronization job.

Execute these SQL Queries on source mdb to force a full mdb synchronization :
Replace <uuid_found_in_step3> by the UUID found in previous step.

USE mdb

UPDATE ca_synchronization_status
SET modified_status=0
WHERE synchronization_conf_uuid in
(SELECT synchronization_conf_uuid FROM ca_synchronization_conf WHERE direction>0 and domain_uuid=(select set_val_uuid from ca_settings where set_id=1)
and enterprise_uuid=<uuid_found_in_step3>)

UPDATE ca_synchronization_status
SET extlstat=NULL
WHERE synchronization_conf_uuid=
(SELECT synchronization_conf_uuid FROM ca_synchronization_conf WHERE table_name='ca_discovered_software' and direction>0 and (where_clause='' OR where_clause is NULL)
and domain_uuid=(select set_val_uuid from ca_settings where set_id=1) and enterprise_uuid=<uuid_found_in_step3>)

Example :