locked
Update Inactive user fires reporting DB error RRS feed

  • Question

  • we have an issue with any Inactive user or resource, if we tried to re-activiate any account an error fires related to updating reporting DB "Reporting (Resource Sync)".

    the error as below:

    • Reporting message processor failed:
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='0205542b-4455-4eb2-bb08-64b771c93ec6' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='2b640b58-03cf-4c8a-9408-a5730c63a17b' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='c28283f4-4ac2-4d02-8609-cec196fd2b78' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='9c49a0c8-bb45-4d12-9b14-092e9ee06a72' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='814d6fc4-a679-41fb-9f97-e8fb648eed8b' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.
      • ReportingResourceChangeMessageFailed (24008) - Violation of PRIMARY
        KEY constraint 'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.. Details: id='24008'
        name='ReportingResourceChangeMessageFailed'
        uid='c9d75b9f-f89f-4d3f-ba88-f794a3183ce0' QueueMessageBody='Resource UID:
        '00d78155-6da2-4eeb-9c5d-0eaa82d4f5b6'. ChangeType='Alter'.
        ResourceChangeType='All'' Error='Violation of PRIMARY KEY constraint
        'PK_MSP_EpmAssignment'. Cannot insert duplicate key in object
        'dbo.MSP_EpmAssignment'. The duplicate key value is
        (0000cf75-fb12-4ffc-a404-aec4f3258a9c, 7a1aa05f-c349-4274-a7a5-3986d3fb9faf).
        The statement has been terminated.'.

    Thanks & Regards Ahmed Amin

    Sunday, June 16, 2013 3:58 PM

Answers

  • In this scenario, when you deactivate a resource, all the Administrative timesheet lines in reporting database will be assigned to a Generic Resource. Timesheet Line UID will be the assignment UID and it will be inserted into msp_assignment and MSP_Assignmentbyday.

    When you try activating the same resource, all these entries will be inserted again to the same table with the same TimesheetLine UID thus creates Duplicate entries

    To resolve, we have to delete these entries from both the table before you can activate the resource. When you activate the resource all these entries will be inserted back.

    As this requires change in DB, needs to be done under expert supervision and assistance. Advise you to open a support case with Microsoft Project support team to assist you on the issue.

    Tuesday, June 18, 2013 6:24 PM

All replies

  • Ahmed,

    As per the error log sounds like this particular resource has duplicate assignment in 'PK_MSP_EpmAssignment'.

    So you  might need to review all the project plans this user has been assignment to, Try to publish those project plans which will report similar error. Once you fix problematic project plans you will be able to save the resource


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Monday, June 17, 2013 3:36 AM
  • Thanks Hirishi,

    I have searched the DB for the project causing the above error, and i found that the UID of this project is mentioned inside the error.

    The issue now the UID is not related to project, it is related to administrative time reported earlier in timesheets.

    any idea to solve this issue.


    Thanks & Regards

    Ahmed Amin


    Monday, June 17, 2013 8:14 AM
  • Thanks for the info

    I am not sure if this will work since user account is inactive, but still worth to give a shot.

    Log on as the user using delegation feature,

    Recall the bad timesheet, delete, recreate and resubmit the same


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Tuesday, June 18, 2013 1:49 AM
  • In this scenario, when you deactivate a resource, all the Administrative timesheet lines in reporting database will be assigned to a Generic Resource. Timesheet Line UID will be the assignment UID and it will be inserted into msp_assignment and MSP_Assignmentbyday.

    When you try activating the same resource, all these entries will be inserted again to the same table with the same TimesheetLine UID thus creates Duplicate entries

    To resolve, we have to delete these entries from both the table before you can activate the resource. When you activate the resource all these entries will be inserted back.

    As this requires change in DB, needs to be done under expert supervision and assistance. Advise you to open a support case with Microsoft Project support team to assist you on the issue.

    Tuesday, June 18, 2013 6:24 PM
  • Thanks Shazeb,

    i have solved this issue by deleting all assignments for these resources related to administrative time and it works fine.

    but it take a lot of time for me to solve it for all Inactive resources.


    Thanks & Regards Ahmed Amin

    Saturday, July 6, 2013 11:48 AM