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
-
- 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)
-
- 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)
- Have any user that has opened the affected project clear their local cache in order to prevent the issue from recurring
- Republish the project to confirm the Reporting queue job completes successfully
Be sure to comment or Contact Us with any questions.
Good Luck!
-Dan