Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

Project Server 2010: Orphan baselines breaking the reporting publish

Project Server 2010: Orphan baselines breaking the reporting publish

Rate This
  • Comments 15

*** Update 7/31/2013 - This was fixed in the June 2012 Cumulative Update for Project 2010 – and was a client fix - http://support.microsoft.com/kb/2598351.  Customers can load the latest Cumulative Update for Project 2010 and it will include this fix.  The fix will stop the problem happening again but does not fix up any existing issues – so they may need to clean up the database using the guidance below.  If you need help then open a support incident with us – we do not charge for bug related incidents – so we can help you through this.  You should also use the feature on the server to control which patch level  of Project 2010 can connect to Project Server to ensure that this problem is not re-introduced by a user with an unpatched version of Project 2010. ***

 

 

This problem has been around for a while and I know some customers were running into it very soon after the release, but we had been struggling to get a repro and understand exactly what was causing it.  We now understand the root cause and have a fix coming hopefully in the June 2012 Cumulative Update for Project Professional 2010 (no promises – but that is the current target) and there are some ways of working that can limit your chances of running into this – so decided we should share this to avoid continued inconvenience until we get the fix out there.

First lets take a look at the symptoms.  The most usual indication of the problem, as the title suggests, is orphan baseline values leading to the error when publishing – a Failed But Not Blocking Correlation problem on a Reporting (Project Publish) job that will show several of the following errors if you click through for the error details:

ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "ProjectServer_Reporting", table "dbo.MSP_EpmTask". The statement has been terminated..

GeneralQueueJobFailed (26000) - ReportingProjectPublish.ReportProjectPublishMessageEx

These failures are for the reporting job – so will mean that reports based on the reporting database, and any fresh OLAP cube builds could be missing data.

Sometimes there may also be a crash on saving, either with a fairly generic MSSOAP 16 Send Incomplete error from Project Professional 2010 (though a subsequent save will work fine), or from PWA a queue error -

GeneralQueueException (9131) A Project Operation failed due to a Queue Exception. Sub Job ID is: . Exception details are: System.NullReferenceException: …at Microsoft.Office.Project.DataEdit.Assignments.AssignmentCalendarUpdateHelper.ConvertActualContourToElapsed(,,,

There may then be issues with users accessing timesheets – The view failed to load.  Press OK to reload this view… (and OK will not help).

image

The error that will be found in the ULS logs will refer to a Calendar whose UID cannot be found…

Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Statusing.StatusingGetMyWorkForGridJson System.InvalidOperationException: CacheProjectBaseCalendars could not find project calendar for project. CalUid=0c13de33-2a07-4310-b091-c77990d9dd6a   

The root of all these issues is that when you use any of the Save & Send options (XML, CSV, Excel etc.) that we are incorrectly changing some of the GUIDs associated with entities such as the tasks and calendars.  Now this isn’t affecting the main tasks and assignment GUIDs as these bad values are not persisted back to the database – but we do however create a new baseline for these non-existent new task GUIDs, and can also save a bad calendar GUID – which leads to the Timesheet problem.

First the best way to avoid this issue, and then on to the detection and clean up at the database level.

If you do need to use Save & Send then the best practice until we release the fix for this is to first save the plan to the server, and publish if you need to.  Then do whatever you need to with Save & Send, and then immediately after this – close and check in the plan – but do not re-save to the server.  Discard changes if it asks – but of course you will have needed to save BEFORE you did the Save & Send (just making sure you are paying attention) to avoid losing any changes you really needed.  As the bad stuff will also get persisted to the local cache, this is one of those rare occasions when you will find me suggesting that the project is removed from the local cache – after ensuring that the save and check-in completed successfully.

WARNING – the following steps are direct queries against the Project Server databases – please be sure you are working against the right databases when using these – and have a database backup should any problems occur.

The detection of this condition is pretty straightforward, as we are just looking for baselines that exist for a task that does not exist, so the following query executed against the Draft database will do this (Change the name to match your specific DBs – the default ProjectServer_ names are used below:

-- Detect for orphan baseline task records that can cause reporting publish job failures.

USE ProjectServer_Draft -- specify the appropriate draft database

select PROJ_NAME, MTB.PROJ_UID,TASK_UID,TB_BASE_NUM from MSP_TASK_BASELINES MTB
inner join MSP_PROJECTS MP on MTB.proj_uid=MP.proj_uid
where TASK_UID not in (select TASK_UID from MSP_TASKS)

This will return rows if the condition exists – and identify which projects – as before clean-up you will probably want to get them removed from the PM’s local cache as otherwise they could be re-introduced.

The next scripts do the cleaning up in the DB, and they are simply deleting baseline records where the tasks are non-existent.

-- Script to run on the draft DB
USE ProjectServer_Draft -- specify the appropriate draft database

delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

-- Script to run on the published DB
USE ProjectServer_Published -- specify the appropriate published database

delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

I hope this helps to understand the nature of the issue and ways to avoid it until the fix comes along.  Our apologies for the inconvenience I know this has caused many of our customers – and hopefully for those who have needed to re-run the clean-up scripts regularly this may give a way to reduce the pain.

If you need any assistance with these steps then feel free to open a support incident – and when I say free I mean free – this is a bug and we do not charge for incidents that are due to bugs (or we will refund – which amounts to the same thing).

The ULS log entry associated with the initial Queue errors above (for the benefit of the search engines):

05/01/2012 11:57:55.67    Microsoft.Office.Project.Server (0x1D74)    0x335C    Project Server    Reporting    atwj    Critical    Standard Information:PSI Entry Point:   Project User: REDMOND\brismith  Correlation Id: e1f4e953-7dea-448a-a528-709075c698bf  PWA Site URL: http://brismith8100/PWA  SSP Name: Project Server Service Application  PSError: ReportingProjectChangeMessageFailed (24006) RDS: The request to synchronize change(s) to project Project UID='216733b0-e194-469a-afc3-9235da4ce4c1'. PublishType='ProjectPublish' failed.  Message: 'ReportingProjectChangeMessageFailed'. Message Body: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "ProjectServer_Reporting", table "dbo.MSP_EpmTask".  The statement has been terminated. Error:(null)    e1f4e953-7dea-448a-a528-709075c698bf

and for the Timesheet error:

05/01/2012 12:13:29.65    w3wp.exe (0x2444)    0x23D8    Project Server    Task Statusing and Updates    btw9    High    CacheProjectBaseCalendars: could not locate data for calendar 0c13de33-2a07-4310-b091-c77990d9dd6a for project 216733b0-e194-469a-afc3-9235da4ce4c1    e5dd4eaf-551a-469b-a3e0-1f60e2f3d1af

05/01/2012 12:13:29.85    w3wp.exe (0x2444)    0x23D8    Project Server    General    0000    Exception    Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Statusing.StatusingGetMyWorkForGridJson System.InvalidOperationException: CacheProjectBaseCalendars could not find project calendar for project. CalUid=0c13de33-2a07-4310-b091-c77990d9dd6a     at Microsoft.Office.Project.Server.BusinessLayer.TimePhasedDataAccess.CacheProjectBaseCalendars()     at Microsoft.Office.Project.Server.BusinessLayer.TimePhasedDataAccess..ctor(StatusingPageLoadDataSet dataset)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.ReadStatusTimephasedDataForResource(IList`1 gridChanges, Guid[] vAssnUids, IDictionary`2 assn2proj, StatusingTimephasedPeriod[] tpdPeriods, DateTime tpStart, DateTime tpEnd)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.<>c__DisplayClass57.<CreateTimephasedDataColumnFiller>b__56(IEnumerable`1 Keys)     at Microsoft.SharePoint.JSGrid.GridSerializer.BuildOutput()     at Microsoft.SharePoint.JSGrid.GridSerializer.ToJson(Serializer s)     at Microsoft.SharePoint.JsonUtilities.Serializer.SerializeToJson(Object o)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.GetMyWorkForGridJson(JsGridSerializerArguments gridSerializerArgs, String gridChangesJson, String projectAssignmentsMap, Guid viewUid, String timephasedStart, String timephasedEnd, Byte pane, Int32 durationType, Int32 workType, Int32 dateFormat, Boolean clearPersistedProperties, Nullable`1 rowFilterType)     at Microsoft.Office.Project.Server.Wcf.Implementation.PWAImpl.StatusingGetMyWorkForGridJson(JsGridSerializerArguments gridSerializerArgs, String gridChangesJson, String projectAssignmentsMap, Guid viewUid, String timephasedStart, String timephasedEnd, Byte pane, Int32 durationType, Int32 workType, Int32 dateFormat, Boolean clearPersistedProperties, Nullable`1 rowFilterType)    e5dd4eaf-551a-469b-a3e0-1f60e2f3d1af

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • good day Brain!

    I fased similar situation for MOPS 2007 after installation April CU.

    Standard Information:PSI Entry Point:

    Project User:

    Correlation Id: b0df3541-e9e2-442a-ae62-553d07d9139f

    PWA Site URL: http://.../pwa

    SSP Name: SharedServices

    PSError: ReportingProjectChangeMessageFailed (24006)

    RDS: The request to synchronize change(s) to project Project UID='fa51b8e3-9b78-4cf3-9890-75cf2af4d91f'. PublishType='ProjectPublish' failed.

    Message: 'ReportingProjectChangeMessageFailed'

    Error:Object reference not set to an instance of an object.

    I receive such mistake at the publication of each\any project.

    your select for draft DB returned NULL)

    have you any ideas?

  • Hi DFS, this certainly isn't the issue you are facing so not suprised the select returned NULL.  However, in researching I found an issue I hadn't been aware of with the same error which has been occuring for customers since the February CU 2012 for Project Server 2007 was released - so I am assuming you hadn't loaded that one but just loaded the April one (which would include the same problem).  The issue relates to a fix we did in February for baseline costs where it is missing a NULL check so can get the error you mention.  We should be releasing a fix for this with the June CU - but I will try and find out if we have a workaround - the only one I see currently is to remove the baseline - which I'm guessing will not a be a good option for many of our customers.  Sorry for the inconvenience this has caused you.  I will probably do a full blog posting on this issueonce I have more information.

    Best regards,

    Brian.

  • (Comment from Brian Smith - although Walter's solution can work in certain scenarios when this error appears - it is not applicable to these conditions - see my full reply to Walter below)

    Hi DFS, I had same error on 2007 and 2010 like you, not the same error reported up.

    Yo can recreate the Reporting Database.

    1. Make an Administrative Backup of the Custom Fields

    2. Restore the Custom Fields

    This will take significant time, so do this on a nonworking time and don´t let it alone, stay monitoring the Project Server Jobs to certain it finish well.

    Hope this help you.

    Regards

    WCC

    wcastillo@epmworks.com

  • Thank you, colleagues.

    dear Brian!

    i'm instaling all CU) but after February CU we didn't have this error...

    i'm expecting your full blog posting with description

    Walter, it's not good idea))

    now i have about 2000 tasks which ended with errors

    have a nice day!

  • Hi Walter, it is possible in some cases that a reporting DB refresh will overcome these errors - but only in cases where there have been un-corrected failures earlier in reporting jobs - such that, for example, a custom field was used in a plan but for some reason the metadata for that field was never correctly sent to the reporting DB (usually due to a queue job that failed and should have been re-tried).  In thsi type of case your solution would work.  What we are talking about with these cases are bugs that will NOT be overcome with an RDB refresh - and in fact as DFS points out - it will mean he is in a worse state than before - as now he will have many more fialed jobs and a more incomplete reporting database.

    Please only offer solutions where you are confident that your answer is the correct one.

    Best regards,

    Brian.

  • DFS - looking deeper into this one and it appears the issue relates to plans where there is a baseline that does not have a cost contour.  Still digging to get a good definition of exactly when this can occur - but assume this might be for a baseline that has work but no cost.  As mentioned we do have a fix coming along.  So far we haven't seen this too often - it would certainly help if your company could open a support incident as it may help expedite the fix.  Once you have an incident open then e-mail me at brian.smith@microsoft.com and loop me in with whichever engineer you are working with.  As this is a bug (quote OfficeQFE 33270) there will be no charge (or there will be a refund/non-decrement) - depending on the type of case.

    Best regards,

    Brian.

  • Hi Brian!

    ok, i will try to open case, and after that e-mail to you.

    Thank you.

  • Hi Brian,

    I can confirm the issue that DFS mentions. I just wrote an E-Mail to you with the case number opened some days ago.

    Regards

    Christoph

  • So was this issue resolved in the June CU?  I just read through what was fixed in that CU and I don't see any reference to this issue as being resolved.

    We are currently running the April CU and are experiencing the exact problems listed above.  I've run the queries found 955 entries between two projects.  I can do the clean up however I'd like to get a permanent fix.  I just wanted to get verification that the fix is included in the June CU.

  • Yes Frank - it was fixed in the client CU support.microsoft.com/.../2598351.  "•Assume that you open a project in Project Server 2010. When you save the project in a different file format, the project on the server contains orphan baseline records." As the bad orphan baseline records can be persisted in the local cache it is also a good idea when loading the June CU to the client to clear projects from the local cache - of course making sure they are saved and checked in first.

    Best regards,

    Brian.

  • Thank you very much Brian! Very helpful :)

  • Hi Brian

    On a similar topic, I've seen an orphan issue on the Baseline by Day table:

    Project 2013 on prem, patched to Oct13CU

    ReportingProjectChangeMessageFailed(24006) = The Insert Statement confliced with the FOREIGN KEY constraint

    "FK_MSP_EpmAssignmentBaselineByDay_ProjectUID_AssignmentUID".  The conflict occurred in the MSP_EPMAssignmentBaseline Table.  Scan count 0, logical reads 98622....etc.etc...

    This seems new to me.  Any thoughts?

  • Carl, I'm seeing the same as you. Project Server 2013, April 2014 CU.

  • Hello Brian,

    Simular problem as Carl. (Project Server 2013, April 2014 CU)

    • ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentBaselineByDay_ProjectUID_AssignmentUID". The conflict occurred in database "SharePoint_Project_003", table "dbo.MSP_EpmAssignmentBaseline". The statement has been terminated.. Details: id='24006' name='ReportingProjectChangeMessageFailed' uid='61e2d71b-dfe0-e311-9423-00505694197f' QueueMessageBody='Project UID='231d4fab-e08b-44fb-8d49-04f7deff207d'. PublishType='ProjectPublish'' Error='The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentBaselineByDay_ProjectUID_AssignmentUID". The conflict occurred in database "SharePoint_Project_003", table "dbo.MSP_EpmAssignmentBaseline". The statement has been terminated.'.

    Is there a SQL script to fix this?

    Thanks!

  • My problem is solved for Project 2013

    social.technet.microsoft.com/.../project-server-2013-reportingprojectchangemessagefailed-24006-the-insert-statement-conflicted

Page 1 of 1 (15 items)