Query to delete cancelled tasks leaves orphaned records

[Carl Paul]

I have a concern that the query posted on this web site and the MS Project
Experts Web sight to delete cancelled “tasks†in PWA will leave orphaned
records in several tables and cause a failure of relational-integrity. I have
included the query below.

There are two problems with this query:

1. MSP_WEB_ASSIGNMENTS is the logical parent of several tables. All the
child records will become orphaned (inaccessible) in the following tables:

Table
--------------------------------------
MSP_WEB_WORK
MSP_WEB_DELEGATION_ASSIGNMENTS
MSP_WEB_MESSAGES_ASSIGNMENTS
MSP_WEB_TRANSACTIONS
MSP_WEB_WORK_ADJUSTED
MSP_WEB_WORK_APPROVAL
MSP_WEB_WORKGROUP_FIELDS


2. WASSN_ID is a foreign key in at least one instance. If there is a inner
join through this foreign key field, the whole join will fail and no joined
“row†will be returned up the tabular data stream… data will disappear when
joins are made using invalid foreign keys:

Table Foreign Key Fiel
-------------------------------------- --------------------------------------------------
MSP_WEB_ASSIGNMENTS WASSN_PARENT_ID




Update Query
-------------------------------------------------------------
delete from MSP_WEB_ASSIGNMENTS
where WASSN_ID IN (
select ma.WASSN_ID from MSP_WEB_ASSIGNMENTS ma
join MSP_WEB_PROJECTS mp
ON ma.WPROJ_ID = mp.WPROJ_ID
join MSP_WEB_RESOURCES mr
ON ma.WRES_ID = mr.WRES_ID
where
ma.WASSN_DELETED_IN_PROJ <>0
--AND mp.PROJ_NAME like '25713%' --uncomment it for specific project
)

 

[Carl Paul]

Please move this to the Project Server discussion thread