locked
ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". RRS feed

  • Question

  • Hi,

    I'm using Project Server 2010 and there is one project that gets Saved and Published but, at the end, there is an error saving to PWA-ProjectServer_Reporting database.

    The error is:

    General

    Reporting message processor failed:

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='0d18542b-6d57-4246-9ad2-f34e654472e2' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='fde3907d-8d9b-414b-9755-ba064d6728fb' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='dd666dce-ce4c-4559-b173-156e742c1162' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='282b4245-2b46-4e5e-891b-c5c497c0a7db' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='32b68684-e37f-49f8-b881-233ac830df79' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='db48827c-ebdf-4eeb-ae96-6c3db1c4172b' QueueMessageBody='Project UID='f11349e4-eb2d-40b1-9d0e-c239fa4ce01b'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "PWA_ProjectServer_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.'.

    Queue:

    GeneralQueueJobFailed (26000) - ReportingProjectPublish.ReportProjectPublishMessageEx. Details: id='26000' name='GeneralQueueJobFailed' uid='cdedd9ff-577c-4c33-bdba-eb8cee1986fb' JobUID='e703f7bb-4eaa-4735-984b-0950deb11646' ComputerName='CUESTOR' GroupType='ReportingProjectPublish' MessageType='ReportProjectPublishMessageEx' MessageId='1' Stage=''. For more details, check the ULS logs on machine CUESTOR for entries with JobUID e703f7bb-4eaa-4735-984b-0950deb11646.

    I've been checking the table PWA_ProjectServer_Reporting.dbo.MSP_EpmAssignment and it has a lot of records for this project 'f11349e4-eb2d-40b1-9d0e-c239fa4ce01b', I checked, too, the table PWA_ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay and there is no record at all for this project. I don't know what it tries to insert in this table, but for sure there is no record inserted.

    I don't know what more to do.

    Please, can you help me on this issue?

    Thank you and regards,

    José Espases

    Thursday, September 22, 2011 2:17 PM

Answers

  • Hi José,

    consider the following thread where i encountered the same error message (error message partly in german):

    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/778c9849-e796-4daa-9727-d39f0f226e31

    Basically, what helped me, was saving the project with "Save as..." on the server and deleting the old version from the server (Draft + Published). This creates new GUIDs for your project objects (tasks, assignments,..) and thus resolves the foreign key conflict.

    WARNING: this approach destroys the links to the existing timesheet information for your project, so you have to take into account if you are willing to solve the problem this way for the price of loosing the timesheets reference.

    Best regards

    Philipp



    • Edited by PhilippH Thursday, September 22, 2011 2:52 PM
    • Marked as answer by José Espases Friday, September 23, 2011 7:59 AM
    Thursday, September 22, 2011 2:48 PM

All replies

  • Sounds like corruption somewhere in your plan. Check for obvious things like tasks with blank names (a semi common issue), check that there are not assignments to resources that are no longer valid.  I would also save a copy of the plan locally as a .xml file and try to republish if I could not find anything obviously wrong.
    Thursday, September 22, 2011 2:34 PM
  • Hi Jose,

    I would run a SQL trace at the time of the publish job and this should identify the issue, one of my colleagues had this issue and found the cause. This is explained here:

    http://pwmather.wordpress.com/2011/07/15/projectserver-2010-reporting-sync-job-failed-foreign-key-constraint-ps2010-project/

    This could be quite a time consuming process to identify the issue. As John mentioned, saving out as XML and reimporting might fix the issue, it didn't in the case above.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Thursday, September 22, 2011 2:48 PM
  • Hi José,

    consider the following thread where i encountered the same error message (error message partly in german):

    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/778c9849-e796-4daa-9727-d39f0f226e31

    Basically, what helped me, was saving the project with "Save as..." on the server and deleting the old version from the server (Draft + Published). This creates new GUIDs for your project objects (tasks, assignments,..) and thus resolves the foreign key conflict.

    WARNING: this approach destroys the links to the existing timesheet information for your project, so you have to take into account if you are willing to solve the problem this way for the price of loosing the timesheets reference.

    Best regards

    Philipp



    • Edited by PhilippH Thursday, September 22, 2011 2:52 PM
    • Marked as answer by José Espases Friday, September 23, 2011 7:59 AM
    Thursday, September 22, 2011 2:48 PM
  • Hi All,

    Thank you for your help, at the end the easiest solution has been the best, just saving in local and deleting in Draft and Published and then uplading from local copy and Saving/Publishing in the server... Now it works fine.

    Thank you and regards,

    José Espases

    Friday, September 23, 2011 8:04 AM
  • Hi José,

    as your problem has been solved already using delete / upload local copy, for you my suggestion will be too late. But perhaps other people can use it.

    I just had the same issue and found this thread.

    In my case it helped to delete just the published version of the project plan. Then open the plan in Project Professional and use the command "save for sharing" that can be found under "save and send.

    By this you create a local copy of the plan somewhere on your computer. Then - without closing the plan (you could, but not needed for the procedure) use the "save as" command. You will find the dialog prefilled with the information from the server.

    Save it and publish it again.

    The advantage of this method is that the GUIDs of project, tasks and assignments are still the same afterwards. You will not loose any information like non-approved status updates or assignment transaction history.

    The only thing you will have to do ist change the owner back to the original project manager as the plan will be "yours" after this procedure.

    Regards

    Christoph

    • Proposed as answer by João Madeira Friday, November 30, 2012 4:28 PM
    Saturday, January 21, 2012 4:39 PM
  • Thank you Christoph,

    yes, it is a little bit late but, anyway, I will use it if by chance I get this error again.

    Regards,

    José Espases

    Monday, December 3, 2012 8:15 AM
  • Worked great for me! Besides changing the owner back it also forced me to create a new project site so I had to go into Operational Policies\Projects Sites and direct it to the old site. Thanks for the fix!
    Thursday, April 25, 2013 4:34 PM
  • Thanks for all the suggestions above.  Relevant to this thread, the approach described in deleting from only draft and not published database did not work for us.  We didn't want to delete from both because we are using timesheets and didn't want a new ProjectUID and orphaned lines.  We did find that simply overwriting (taking new) baselines allowed for the Project to be saved and published without error.  We were ok with overwriting baselines, but if you need to preserve that data, perhaps you could identify just those tasks without baselines and manually copy and paste from planned to the respective baseline number(s).  That might be enough to clear the queue error. Good luck!

    Justin Naughton

    Friday, May 16, 2014 7:10 PM