none
Inconsistencies in reporting database assignment table (PS2013) RRS feed

  • Question

  • I am seeing numerous inconsistencies in our reporting database MSP_Assignment table.

    We have some resources with assignments that are correct in the published database tables but when we look at the reporting tables I see assignments that have the "Unassigned Resource" RES_UID set, rather than the correct resource as shown in the published database.

    When I query the pub.MSP_Assignments table for the suspicious task assignments, I can see assignment rows that have correct data as expected. When I query the dbo.MSP_Assignment table for the same assignment UID, I see the record but with incomplete data and with the "Unassigned Resource" UID rather than the correct resource from the enterprise resource pool.

    I have tried numerous steps to correct the problem, including republishing the projects, rebuilding the reporting database, unpublishing and republishing the task, all to no avail.

    This problem is causing us not to be able to rely on our reporting data and it is a deal breaker for us if we can't get this fixed. Has anyone seen this before?


    • Edited by Dale Kirby Monday, March 18, 2013 4:07 PM
    Monday, March 18, 2013 3:22 PM

Answers

  • We have resolved this issue by using the "Save for Sharing" technique.

    1. From Project 2013, open the project and from the File menu click "Share"
    2. Choose "Save for Sharing" and save the file to a local drive.
    3. With the same project still open, go back to the File menu and choose "Save As"
    4. From the dialogue, click Save to save the file using the same file name.

    As I understand it, this procedure will completely sync the dbo schema with the pub schema if a corruption issue has manifested (e.g. the missing assignment data we were seeing).

    This resolved my issue and I am proposing this as the answer.

    • Marked as answer by Dale Kirby Friday, April 26, 2013 10:55 PM
    Friday, April 26, 2013 10:55 PM

All replies

  • I've opened a ticket with MS support on this issue and we are looking into it. Has no one else seen this behavior? If I can provide more info that would help describe the issue, please let me know.
    Friday, March 22, 2013 12:52 AM
  • We have resolved this issue by using the "Save for Sharing" technique.

    1. From Project 2013, open the project and from the File menu click "Share"
    2. Choose "Save for Sharing" and save the file to a local drive.
    3. With the same project still open, go back to the File menu and choose "Save As"
    4. From the dialogue, click Save to save the file using the same file name.

    As I understand it, this procedure will completely sync the dbo schema with the pub schema if a corruption issue has manifested (e.g. the missing assignment data we were seeing).

    This resolved my issue and I am proposing this as the answer.

    • Marked as answer by Dale Kirby Friday, April 26, 2013 10:55 PM
    Friday, April 26, 2013 10:55 PM
  • Hi Dale,

    I can confirm we're seeing the same issue here on a regular basis. My solution so far was to export the Project to an XML file, delete the full project from the server, then import the XML and save as a new project on the server. Quite painful each time it happens.

    A fix from Microsoft would raise our trust again as today we always have the feeling our reporting environment might be missing important information... That's not a situation you want to be in.

    I will try the "Save for Sharing" technique next time we spot such an inconsistency in our Reporting Database.

    Thanks,

    Kjell

    Monday, June 24, 2013 9:37 AM
  • The "Save for Sharing" technique has indeed worked for us as well.

    Any updates so far on the status of the ticket at MS ?

    Monday, June 24, 2013 10:40 AM
  • Hi

    we have the same issue in several environments. Not with all but some assignments. There had been fixes with the reporting tables in October 2013 CU but these do do solve the issue in our case.

    I have my own bad workaround for it - a SQL job comparing the resources in the MSP_EPMAssignment table against the one in pub.msp_assignments and fixing the issue if needed.

    update MSP_EpmAssignment
    set ResourceUID= a.res_uid from
    MSP_EpmAssignment RA inner join pub.MSP_ASSIGNMENTS a on ra.AssignmentUID=a.assn_uid
    inner join pub.msp_resources r on a.res_uid=r.res_uid
    where ra.ResourceUID !=a.RES_UID

    update MSP_EpmAssignment
    set ResourceOwnerUID= a.RES_UID_OWNER from
    MSP_EpmAssignment RA inner join pub.MSP_ASSIGNMENTS a on ra.AssignmentUID=a.assn_uid
    inner join pub.msp_resources r on a.RES_UID_OWNER=r.res_uid
    where ra.ResourceOwnerUID !=a.RES_UID_OWNER  

    Regards

    Christoph


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Monday, November 4, 2013 2:32 PM
  • Hi all,

    I'm getting the error as well with SP1 + June CU 2014.

    The Save for Sharing at least helped me to repair one plan.

    The issue seems to occur when you assign resources via the MS Project Web App and not via the MS Project Pro client.

    Regards,

    Trutz

    Wednesday, July 16, 2014 9:15 PM
  • Hi Dale,

    We have also seen this in our environment. From our side we found that deleting the plan from the Pub/Rep schemas via the front end and republishing has fixed the problem.

    I'm curious, however, if Microsoft provided any guidance on how this has occurred, if they have fixed that in a later CU and if you have seen any re-occurrences of that since you fixed the assignments in question?

    Thanks for your time,

    Cheers,

    Shane


    Shane O'Hanlon (Accenture)

    Thursday, June 18, 2015 2:16 PM