Importation with contentutility tool imports nothing into the database

Products

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

Issue/Introduction

contentutility.exe tool is used to export/import data from a Domain with Internet connection to a Domain without internet connection.

Importation on Domain without internet connection is executed without displayed error but in database nothing is imported.

Environment

Client Automation - All Versions.

Cause

A possible cause of empty importation is if ca_itrm SQL user has sysadmin rights.

 

Explanation :

Files are imported in temp tables like ca_itrm.conttmp_ca_company

 

Some command lines like this are executed during importation :

 

"cmd" /c bcp mdb.ca_itrm.conttmp_ca_company in "C:\ProgramData\CA\software_definitions\ca_company" -w -S <sqlservername> -U ca_itrm -P <ca_itrm_pwd> -o "C:\ProgramData\CA\software_definitions\ca_company.out" -e "C:\ProgramData\CA\software_definitions\ca_company.err"

 

But contentutility executes the SQL Query without ca_itrm prefix in tablename.

 

 

Example :

drop table conttmp_ca_company_type

create table conttmp_ca_company_type (id int NOT NULL,inactive int,name nvarchar (100) COLLATE Latin1_General_CI_AS,creation_user nvarchar (64) COLLATE Latin1_General_CI_AS,creation_date int,last_update_user nvarchar (64) COLLATE Latin1_General_CI_AS,last_update_date int,description nvarchar (255) COLLATE Latin1_General_CI_AS,tenant binary (16))

alter table conttmp_ca_company_type with nocheck add constraint conttmp_ca_company_type_pk primary key clustered (id)

update ca_company_type set inactive=tmp.inactive,name=tmp.name,creation_user=tmp.creation_user,creation_date=tmp.creation_date,last_update_user=tmp.last_update_user,last_update_date=tmp.last_update_date,description=tmp.description from conttmp_ca_company_type as tmp where  ca_company_type.id=tmp.id 

insert into ca_company_type (id,inactive,name,creation_user,creation_date,last_update_user,last_update_date,description) select id,inactive,name,creation_user,creation_date,last_update_user,last_update_date,description from conttmp_ca_company_type as tmp where not exists (select id from ca_company_type as data where  data.id=tmp.id )

 

If ca_itrm has sysadmin privileges, the default user prefix is dbo

 

So SQL Queries are using dbo.conttmp_ca_company_type.

 

And this table is empty as importation has been made in table ca_itrm.conttmp_ca_company_type

 

It is why nothing is imported without error message.

So the solution is to remove the sysadmin role from ca_itrm user.

 

Resolution

Check in Microsoft SQL Server Management Studio if the login ca_itrm has not the sysadmin server role checked :

 

Uncheck sysadmin role for ca_itrm login will resolve the importation problem.