The below error can be found in the Project Server queue when a project cannot be succesfully published:
General Reporting message processor failed: OR ProjectPublish Failed: ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint
In the majority of cases, the cause of this is due to baselined tasks being deleted from the schedule, causing it to be out of sync with the Reporting Database. The project does not successfully reach the Reporting Database and the publish job fails.
The fix for this depends if it is only affecting 1 or 2 projects or many. It may also be affecting timesheet aggregation jobs to fail. If it is affecting many and you are seeing timesheet jobs failing consistently, use option 2. If it is only affecting 1 or 2 projects, use option 1.
If affecting only 1 or 2 projects:
1. Take a full db backup.
2. Execute the following query on both the Published and Draft databases:
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)
order by PROJ_Name
delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)
3. Republish the trouble projects
A resync of the reporting database can resolve the issue if you have multiple publishing/timesheet errors, however first you should ensure that the sync will be successful by following these steps:
NB: Reporting db syncs can take a long time, particularly with many projects/resources. Do this at the quietest time possible to avoid further issues:
1. Take a full backup
2. Execute the same query as posted above to the published and draft dbs
3. Go to Server settings>Administrative Backup and backup the Enterprise Custom Fields
4. Go to Server settings>Administrative Restore and restore the Enterprise Custom Fields
5. Monitor the queue and insert ‘successful jobs’ to watch the sync do its thing. Eventually the ‘Reporting Database Refresh’ job will tick over to be successfully completed.
6. The problem now is that the Enterprise Project Type UID is removed from every single project in the reporting db leaving a NULL value. (For unknown reasons. Ideally this will be fixed!). The problem is that if you use data connections or SSRS reports that reference the EPT, these reports won’t work as expected.
7. To reapply the Enterprise Project Type UIDs you can publish each project individually which will fix the problem, or if you were like me and had 1200 projects, you might like run project publish from the PSI or use a simpler method as follows:
8. Go to Server Settings>Change and Restart Workflows
9. Select all projects from EPT A and associate (again) with EPT A. Repeat this process for each EPT. Be sure to associate them to the same EPT as they originally were.
10. The UIDs will return in the reporting db following this and your reports should return to normal. Obviously, rebuild the Cube if you did this after the nightly build.
Hope this saves someone out there 🙂