Error: ReportingProjectChangeMessageFailed with Microsoft Project Server 2010

Hello,

We recently had a customer who was receiving the error below when publishing one of their projects.  The error was a Reporting job fail, therefore, the reporting database was not reflecting the correct information for the project.  This error is related to baseline records containing incorrect values for certain fields, such as a blank date value rather than N/A, or a blank Work value instead of some numeric value.  In short, the issue is ghost or orphan baseline records.  We performed the procedure below to resolve this issue:

The Error

: ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "2010_PWA_Reporting_DB", table "dbo.MSP_EpmTask". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='30902cda-a8fd-43e8-b465-c43711e0c015' QueueMessageBody='Project UID='6c602bd4-7ccb-4b15-a393-386bc8fb010f'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "2010_PWA_Reporting_DB", table "dbo.MSP_EpmTask". The statement has been terminated.'.

The Fix

1. 1. Run the query below to confirm there are indeed orphan records:

USE ProjectServer_Draft -- specify the appropriate draft database

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)

1. 1. Run the scripts below to delete the orphan records

-- Script to run on the draft DB

USE ProjectServer_Draft -- specify your draft database

delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

-- Script to run on the published DB

USE ProjectServer_Published -- specify your published database

delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

1. Have any user that has opened the affected project clear their local cache in order to prevent the issue from recurring
2. Republish the project to confirm the Reporting queue job completes successfully

Be sure to comment or Contact Us with any questions.

Good Luck!

-Dan

Related

• Cancel Delegate Session in Project Server 2010 via SQL Statements
• My Sites: We’re almost Ready!
• 'Specified cast is not valid’ in Microsoft Project Online & Microsoft Project Server
• Newly added enterprise custom field values not visible in Project Professional 2010
• Microsoft Project Server 2013: Timesheet Failed to load