Hi Anu,

This is caused by orphaned records in the database being extracted into the XER file during export, then the error is received when importing an XER file that contains these orphaned records. Orphaned records can happen for a variety of reasons.

- One known cause is if multiple users are in a project and user 1 runs a global change or fill down for a user defined field and user 2 is deleting an activity at the same time. The activity is successfully deleted but leaves an orphaned UDF value. (Note: It’s recommends running these types of changes in "Exclusive mode".)

- This particular cause of orphaned records has been fixed in Project Management 5.0 SP1 and 4.1 SP4. Applying these releases will not fix pre-existing orphaned records in databases or XER files but will prevent new orphaned records from being created by this scenario. Therefore, the error will continue to happen if the XER being imported contains orphaned UDFs. (see the delete_orphaned_UDFVALUE scripts below for database cleanup of orphaned records.)

This known problem in Project Management has been corrected in version 5.0 Service Pack 2.

Run database queries/scripts on the source database (the database where the XER was exported from). See steps below.

Run the appropriate script below on the database that the XER was exported from
- For Microsoft SQL Server, run the script as PRIVUSER
- For Oracle , run the script as ADMUSER
- For MSDE Primavera 5.0 Standalone installation, save the appropriate script to the root of the C:\ drive and run the following from a DOS command prompt:
(Start > Run > type ’cmd’ and click OK): osql -U sa -P prima -S localhost\primavera -d pmdb$primavera -i c:\delete_orphaned_UDFVALUE.sql

Note: Copy this scrip below on a Note Pad and save as

delete_orphaned_UDFVALUE.sql

RUN THIS SCRIPT ON A BACKUP OF THE DATABASE FIRST!

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’DOCUMENT’)
and fk_id not in (select doc_id from document);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROCITEM’)
and fk_id not in (select proc_item_id from procitem);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROJCOST’)
and fk_id not in (select cost_item_id from projcost);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROJISSU’)
and fk_id not in (select issue_id from projissu);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROJECT’)
and fk_id not in (select proj_id from project);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROJRISK’)
and fk_id not in (select risk_id from projrisk);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’PROJWBS’)
and fk_id not in (select wbs_id from projwbs);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’RSRC’)
and fk_id not in (select rsrc_id from rsrc);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’TASK’)
and fk_id not in (select task_id from task);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’TASKPROC’)
and fk_id not in (select proc_id from taskproc);

delete from udfvalue where udf_type_id in
(select udf_type_id from udftype where table_name = ’TASKRSRC’)
and fk_id not in (select taskrsrc_id from taskrsrc);


If the script seems to hang at the section where the script attempts to delete UDF data which has no corresponding entry in the TASK table, please replace it for:

delete from udfvalue a where exists
(select 1 from udftype b where b.udf_type_id = a.udf_type_id and b.table_name = TASK’)
and not exists (select 1 from task c where c.task_id = a.fk_id);

Regards,
Rodel