Import with contentutility.exe tool is failing with error "failed to import file tablename=ca_link_sw_def"

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

Importation using contentutility.exe tool is failing with error :

 

failed to import file tablename=ca_link_sw_def

Import failed with return code [3]

 

 

 

In TRC_CF_CONTENTUTILITY*.log following error appears :

 

|NOTIFY | evalSQLInfo sqlstate: 23000 native error: 547 0x223
|NOTIFY | evalSQLInfo description: The INSERT statement conflicted with the FOREIGN KEY constraint "$ca_li_r000003db00000000". The conflict occurred in database "mdb", table "dbo.ca_software_def", column 'sw_def_uuid'. source: Microsoft SQL Server Native Client 11.0
|NOTIFY | evalSQLInfo class: 16 state: 1 server:
|NOTIFY | Error Message: ADO Version 10.0 - COM Error: ErrorCode:-2147217873,WordErrorCode:3119, IDispatch error #3119, The INSERT statement conflicted with the FOREIGN KEY constraint "$ca_li_r000003db00000000". The conflict occurred in database "mdb", table "dbo.ca_software_def", column 'sw_def_uuid'., Microsoft SQL Server Native Client 11.0, (null)
|ERROR  | Error inserting into table sql=[insert into ca_link_sw_def (last_update_date,secondary_sw_def_uuid,primary_sw_def_uuid,link_type_id,source_type_id) select last_update_date,secondary_sw_def_uuid,primary_sw_def_uuid,link_type_id,source_type_id from conttmp_ca_link_sw_def as tmp where (source_type_id=1 or source_type_id=2 or (source_type_id=5 and link_type_id = 10) or (source_type_id=6 and link_type_id = 10)) and not exists (select primary_sw_def_uuid,secondary_sw_def_uuid from ca_link_sw_def as data where  data.primary_sw_def_uuid=tmp.primary_sw_def_uuid and data.secondary_sw_def_uuid=tmp.secondary_sw_def_uuid )]
|ERROR  | Failed to insert into table ca_link_sw_def
|ERROR  | Failed to import file tablename=ca_link_sw_def

Environment

Client Automation - All Versions

Cause

There are 2 possible causes :

CAUSE 1 :

A "custom created" Product is linked to some "Heuristic" releases. This is not correct.
To check if there are some rows like this into the database, execute this SQL query on source mdb database (database where export is done) :

 

SELECT p.name 'Custom Created Product Name', p.sw_version_label 'Custom Created Product Version', r.name 'Heuristic Release Name', r.sw_version_label 'Heuristic Release Version'
FROM ca_software_def p
LEFT JOIN ca_link_sw_def l ON l.primary_sw_def_uuid=p.sw_def_uuid
LEFT JOIN ca_software_def r ON l.secondary_sw_def_uuid=r.sw_def_uuid
WHERE p.software_type_id=8 and p.source_type_id=2 and r.software_type_id=3 and r.source_type_id=3 and l.link_type_id=3 and l.source_type_id=2

 

If some rows are returned by this query, apply the solution for cause 1 below.

CAUSE 2 :

Some primary_sw_def_uuid or secondary_sw_def_uuid in table ca_link_sw_def are not present in table ca_software_def.
This is not normal and indicates that some foreign keys are missing on table ca_link_sw_def

Execute following queries on source mdb (database where export is made) 

 

select * from ca_link_sw_def where primary_sw_def_uuid not in (select sw_def_uuid from ca_software_def)
select * from ca_link_sw_def where secondary_sw_def_uuid not in (select sw_def_uuid from ca_software_def)

 

If one of these 2 queries returns some rows, apply the solution for cause 2 below.

Resolution

Solution for Cause 1

On source mdb execute following SQL Query : 

UPDATE ca_link_sw_def
SET source_type_id=3
FROM ca_software_def p
LEFT JOIN ca_link_sw_def l ON l.primary_sw_def_uuid=p.sw_def_uuid
LEFT JOIN ca_software_def r ON l.secondary_sw_def_uuid=r.sw_def_uuid
WHERE p.software_type_id=8 and p.source_type_id=2 and r.software_type_id=3 and r.source_type_id=3 and l.link_type_id=3 and l.source_type_id=2

 

Solution for Cause 2

1. Do a backup of source mdb database
   
   
2. On source mdb execute following SQL Query : 
   
   
   delete ca_link_sw_def where primary_sw_def_uuid not in (select sw_def_uuid from ca_software_def)
   delete ca_link_sw_def where secondary_sw_def_uuid not in (select sw_def_uuid from ca_software_def)
   
   

3. Execute this on source mdb database to recreate the missing foreign keys :

   USE [mdb]
   GO
   ALTER TABLE [dbo].[ca_link_sw_def]  WITH CHECK ADD  CONSTRAINT [$ca_li_r000003be00000000] FOREIGN KEY([source_type_id])
   REFERENCES [dbo].[ca_source_type] ([source_type_id])
   GO
   ALTER TABLE [dbo].[ca_link_sw_def] CHECK CONSTRAINT [$ca_li_r000003be00000000]
   GO
   
   ALTER TABLE [dbo].[ca_link_sw_def]  WITH CHECK ADD  CONSTRAINT [$ca_li_r000003c800000000] FOREIGN KEY([link_type_id])
   REFERENCES [dbo].[ca_link_type] ([link_type_id])
   GO
   ALTER TABLE [dbo].[ca_link_sw_def] CHECK CONSTRAINT [$ca_li_r000003c800000000]
   GO
   
   ALTER TABLE [dbo].[ca_link_sw_def]  WITH CHECK ADD  CONSTRAINT [$ca_li_r000003d200000000] FOREIGN KEY([primary_sw_def_uuid])
   REFERENCES [dbo].[ca_software_def] ([sw_def_uuid])
   GO
   ALTER TABLE [dbo].[ca_link_sw_def] CHECK CONSTRAINT [$ca_li_r000003d200000000]
   GO
   
   ALTER TABLE [dbo].[ca_link_sw_def]  WITH CHECK ADD  CONSTRAINT [$ca_li_r000003db00000000] FOREIGN KEY([secondary_sw_def_uuid])
   REFERENCES [dbo].[ca_software_def] ([sw_def_uuid])
   GO
   ALTER TABLE [dbo].[ca_link_sw_def] CHECK CONSTRAINT [$ca_li_r000003db00000000]
   GO