FOREIGN KEY constraint “FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID” Error in Project Server 2010 Queue

Just yesterday I had a client (SP1 & February 2012 CU) who had the following error messages in their Project Server 2010 Queue when trying to publish a project;

ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint “FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID”. The conflict occurred in database “PWA_Reporting”, table “dbo.MSP_EpmTask”. The statement has been terminated..

This one is also mentioned further down in the error;

GeneralQueueJobFailed (26000) – ReportingProjectPublish.ReportProjectPublishMessageEx

These messages are associated with the Reporting Database, and can lead to missing information in both the Reporting DB and the OLAP Cubes. Apparently this error occurs with GUID associations when using the Save and Send functionality.

Anyway, to fix the problem I first backed up the  5 SQL databases. With this done, I proceeded with running the following query to DETECT any projects with the issue;

USE PWA_Draft
Select
PROJ_NAME,
MTB.PROJ_UID,
TASK_UID,TB_BASE_NUM
From
MSP_TASK_BASELINES MTB
Inner Join
MSP_PROJECTS MP
on MTB.proj_uid=MP.proj_uid
Where TASK_UID
Not In (select TASK_UID from MSP_TASKS)

This sure enough displayed the problematic project, so with this, I then run this query against the Draft Database;

USE
PWA_Draft
Delete From
MSP_TASK_BASELINES
Where TASK_UID not in (select TASK_UID from MSP_TASKS)

And finally, the following query against the Published Database;

USE PWA_Published
Delete From
MSP_TASK_BASELINES
Where TASK_UID not in (select TASK_UID from MSP_TASKS)

This then allowed me to rebuild the reporting db, publish the problematic project (cleared the cache first in the users Project Pro client).

Following this, the June 2012 CU should be applied to prevent the issue from happening again.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s