none
Reporting database problem RRS feed

  • Question

  • I'm using Project Server 2010 without any CU's on SQL Server 2008 SP1 CU3 and I have noticed that I have orphan data in the reporting database.

    Specifically - time sheets. I have run the procedure (on the admin section of PWA) to clear out all time sheets and they have disappeared from the published database but not the reporting database.

    The problem has been caused by a project/time sheet data conversion utility (from our old time sheet system) which used the WCF PSI interface. Sometimes I see jobs fail - like these:

    ReportingTimesheetSaveMessageFailed (24014) - Violation of UNIQUE KEY constraint 'UK_MSP_Timesheet'. Cannot insert duplicate key in object 'dbo.MSP_Timesheet'. The statement has been terminated.. Details: id='24014' name='ReportingTimesheetSaveMessageFailed' uid='97413557-c9fb-488c-b77e-e827be35146d' QueueMessageBody='TimesheetUid='bd9ff382-9513-4233-9cf0-b0560cccefba'' Error='Violation of UNIQUE KEY constraint 'UK_MSP_Timesheet'. Cannot insert duplicate key in object 'dbo.MSP_Timesheet'. The statement has been terminated.'.

    ReportingTimesheetDeleteMessageFailed (24011) - Invalid call to MSP_EpmDeleteTimesheetAdminAssignments. Timesheet UID (A058168E-9BD1-4ADA-9804-B47F22AF4D41) was not found in MSP_Timesheet.. Details: id='24011' name='ReportingTimesheetDeleteMessageFailed' uid='b862973a-b664-4f65-a79b-d36018b4e2fe' QueueMessageBody='TimesheetUid='a058168e-9bd1-4ada-9804-b47f22af4d41'' Error='Invalid call to MSP_EpmDeleteTimesheetAdminAssignments. Timesheet UID (A058168E-9BD1-4ADA-9804-B47F22AF4D41) was not found in MSP_Timesheet.'.

    I suspect that this is caused whenever a transaction fails to complete through the PSI due to a communications error (which I get a lot of).

    Is there a safe way to clean this reporting database database of time sheet entries?

    Sunday, October 10, 2010 2:48 AM

Answers

  • Hi Stuart,

    Unfortunately that procedure only refreshes part of the reporting db, and specifically the EPM tables.  There isn't an equivalent for the Timesheets.  Have you tried recalling and deleting the timesheet in question?  Or is that just giving you the second error above?  If so then I am sure we would be able to asist you in cleaning this up via a support incident.  Also worth getting the CU's on your system.

    Best regards,

    Brian.

     


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    • Marked as answer by Stuart Penning Tuesday, October 12, 2010 1:22 AM
    Monday, October 11, 2010 8:31 PM
    Owner

All replies

  • I found this link: http://msfarmer.blogspot.com/2008/12/rebuild-project-server-reporting.html which I am now trying. Hopefully this approach still works on EPM2010
    Sunday, October 10, 2010 2:59 AM
  • Hi Stuart,

    Unfortunately that procedure only refreshes part of the reporting db, and specifically the EPM tables.  There isn't an equivalent for the Timesheets.  Have you tried recalling and deleting the timesheet in question?  Or is that just giving you the second error above?  If so then I am sure we would be able to asist you in cleaning this up via a support incident.  Also worth getting the CU's on your system.

    Best regards,

    Brian.

     


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    • Marked as answer by Stuart Penning Tuesday, October 12, 2010 1:22 AM
    Monday, October 11, 2010 8:31 PM
    Owner
  • Hi Brian, thanks for the info.

    I am not 100% sure how it happened but as I understand it, the reporting synch job that removes the data from the reporting database failed when the time sheet was deleted, leaving the orphan data in the reporting database.At that stage, there were no timesheets to recall because it they were already gone.

    I will keep an eye on the failed jobs in the future and post the error details on this thread, if it happens again.

    This is no longer an issue for me because I had created a separate site on the server for testing (of my timesheet/project data conversion system) and it happened in that testing database. NB the conversion system I speak of uses the PSI (WCF) to create the entries, and does not access the database directly at all.

    I think that it would be good if the refresh could refresh the whole database - is this something that can be requested as a change to the system?

    The process for accessing support is a bit fuzzy to me. Hansen Technologies has recently entered an Enterprise licensing agreement but I am not sure how to go about requesting support under this agreement (or any other way), if I needed it. Could you please point me in the right direction?

    Regards

    Stuart

    Tuesday, October 12, 2010 12:57 AM