Recently one of my colleagues came across this reporting synch job error with Project Server 2010 SP1 + June 2011 CU. In the blog post below the full error is listed along with the resolution for this particular issue and the details on how Frank got to the bottom of the error. I thought I would share this with you in case anyone comes across this in the future.
We received the following error message in the Queue when trying to publish a plan:
ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "TEST_Project_Server_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id=’24006′ name=’ReportingProjectChangeMessageFailed’ uid=’b15f1280-4555-47ca-a7e4-6e3b815183c7′ QueueMessageBody=’Project UID=’ff3a3de8-7af3-4b61-87ed-7424fce5569a’. PublishType=’ProjectPublish” Error=’The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "TEST_Project_Server_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.’.
This indicated to us that there is at least one assignment corrupted in the plan.
By running a SQL trace we could see that Project Server tried to write a time phased entry for an assignment into the MSP_EpmAssignmentByDay table. Examining this further we identified that for the record it tried to write no assignment record had been created in the MSP_EPMAssignment table in the reporting database. Based on the table constraints on the MSP_EpmAssignmentByDay table this was bound to fail.
Hypothesis: Project Server is not checking sufficiently before inserting into the MSP_EpmAssignmentByDay table if the assignment for the time phased entries actually exists in the MSP_EpmAssignment table (reporting database).
It appears that the publishing process is ignoring the assignment record that is present in the MSP_ASSIGNMENT table in the Published database. The reason why it is ignoring the records is unclear to us at this stage.
In order to resolve it we identified the task within the plan that had the issue using SQL Server Profiler. This is a very time consuming process and as such not a viable solution long-term.
Eventually we were able to identify the assignment row within the plan that was causing the error.
Since in this case only local resources were present in the plan (but the same steps should also work with enterprise resources) we followed the following approach to resolve the issue:
1.) Ensure the plan is archived (EPM backup)
2.) Create a new local dummy resource with the same Enterprise Calendar as the resource associated with the assignment/task in question
3.) Substitute the resource on the activity in question with the dummy resource (using the ‘Assign’ functionality in project pro).
Please note: This should usually not affect the duration / work value on a task unless there are resource calendar exceptions defined against the resource to be replaced which are not present for the dummy resource.
Furthermore this process would potentially cause issues if the task was partially or fully progressed.
4.) Save & publish the plan (the schedule did not appear to be affected by this change)
The publish job for reporting when through successfully this time
5.) Substitute the resource back with the original resource and delete the dummy resource from the resource sheet
6.) Re-publish the plan [publishing should work correctly – if not repeat the process and identify if there are more than one assignment that is corrupted in the plan]
The result is that the plan publishes now again. Our concern is that this is likely to be only a temporary measure and the issue will re-occur in due time.
Many thanks to my colleague Frank Merla for sharing this.