none
Tasks missing from reporting database even though they are in the project plan RRS feed

  • Question

  • Hello,

    I am experiencing some wierd phenomenon.

    We are using Project Server 2010, with SharePoint 2010 and SQL Server 2008 R2. We have got a project plan with more than 700 tasks. On this project plan we have task with actual work, remaining work ...

    Ok, so my issue: I discovered that all the tasks tables in Reporting database are not containing tasks. For example:

    • table Task by day User view  is empty
    • table TAsk Userview contain data from timesheet but not task as before

    I am opening the table via Excel in remote desktop.

    I hope I gave you enough informations

    I don't know what is going on or how serious is that issue but everything was working just fine before.

    Thank you by advance for your help

    Morgane

    Thursday, April 4, 2013 8:15 AM

Answers

  • Dear Graham,

    <o:p></o:p>

    I followed your advice about the queue job, I found an error from 3 days ago:

    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".
    <o:p></o:p>

    After some research, I found out that the issue is known by Microsoft. Here are the links I found:<o:p></o:p>

    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/f2afb830-2c51-4b23-8b8d-cb2566b5b113/<o:p></o:p>

    http://nearbaseline.com.au/blog/tag/olap/<o:p></o:p>

    http://blogs.msdn.com/b/brismith/archive/2012/05/01/project-server-2010-orphan-baselines-breaking-the-reporting-publish.aspx<o:p></o:p>

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

    I will let you know if the solution works for us.

    <o:p></o:p>

    Thank you very much for your time and your help

    <o:p></o:p>

    Best greetings<o:p></o:p>

    Morgane<o:p></o:p>


    • Marked as answer by MorganeZ Friday, April 12, 2013 12:35 PM
    Friday, April 5, 2013 12:11 PM
  • Hello everybody.

    So we have done it. Deleting the orphan tasks perfectly worked.

    Thangs again

    Best regards,

    Morgane

    • Marked as answer by MorganeZ Friday, April 12, 2013 12:33 PM
    Friday, April 12, 2013 12:32 PM

All replies

  • Morgane, is it just the data in the reporting database that is missing? Is the data there in other databases (working, published)?

           Graham

    Thursday, April 4, 2013 2:48 PM
  •  Thanks for your answer!

    Actually, it is just the tasks data (timesheet data and other are ok) from the reporting database. the data are still in the published one

    Morgane

    Thursday, April 4, 2013 3:17 PM
  • and ok for draft db too!
    Thursday, April 4, 2013 3:42 PM
  • Good to know. What happens if you republish the project? Is there any error in the processing queue?          Graham
    Thursday, April 4, 2013 10:05 PM
  • Dear Graham,

    <o:p></o:p>

    I followed your advice about the queue job, I found an error from 3 days ago:

    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".
    <o:p></o:p>

    After some research, I found out that the issue is known by Microsoft. Here are the links I found:<o:p></o:p>

    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/f2afb830-2c51-4b23-8b8d-cb2566b5b113/<o:p></o:p>

    http://nearbaseline.com.au/blog/tag/olap/<o:p></o:p>

    http://blogs.msdn.com/b/brismith/archive/2012/05/01/project-server-2010-orphan-baselines-breaking-the-reporting-publish.aspx<o:p></o:p>

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

    I will let you know if the solution works for us.

    <o:p></o:p>

    Thank you very much for your time and your help

    <o:p></o:p>

    Best greetings<o:p></o:p>

    Morgane<o:p></o:p>


    • Marked as answer by MorganeZ Friday, April 12, 2013 12:35 PM
    Friday, April 5, 2013 12:11 PM
  • Morgane, well done. Looks like your persistence is paying off.              Graham
    Friday, April 5, 2013 1:09 PM
  • I am facing the same issue i.e. Custom fields are not updated @ Reporting DB and the project is not listed in the simple projects excel report (when it should be). I am trying to find a solution to for the same.

    So far what i have found is that this type of behaviour is because of some Orphan tasks that need to be deleted to fix the issue from the Draft and the Published DBs. To identify the Projects and Orphan tasks execute the following SQL queries

    USE ProjectServer_Draft

    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)

    USE ProjectServer_Published

    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)

    The above mentioned queries will list down the Orphan tasks. Delete those Orphan task by executing the following queries

    USE ProjectServer_Draft

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

    USE ProjectServer_Published

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

    After deleting the Orphan tasks clear the project professional cache and than Publish the project(s). This will fix the Custom fileds missing values and the project is now listed in the reports.

    Morgane, This might help you to fix the issue that you are facing.

    I have updated the Project Server 2010 with SP-I with December 2012 CU but still somehow the Orphan tasks are created again and for different/random projects and I have to follow the same steps again.

    My Question:  What is causing this type of behaviour and how can this rectified i.e. I cannot execute the query again and again without getting a Approval. Thanks


    Thanks, Siskin.

    • Proposed as answer by Siskin82 Monday, April 29, 2013 11:49 AM
    Sunday, April 7, 2013 10:18 AM
  • Hello,

    @ Graham and @Siskin, thanks a lot for your advices. Let you know when it is solved!

    Siskin that is a good question let me know if you find something about it

    Have a good day

    thanks again

    Morgane

    • Marked as answer by MorganeZ Friday, April 12, 2013 12:30 PM
    • Unmarked as answer by MorganeZ Friday, April 12, 2013 12:31 PM
    Tuesday, April 9, 2013 8:02 AM
  • Hello everybody.

    So we have done it. Deleting the orphan tasks perfectly worked.

    Thangs again

    Best regards,

    Morgane

    • Marked as answer by MorganeZ Friday, April 12, 2013 12:33 PM
    Friday, April 12, 2013 12:32 PM
  • I am facing the same issue again. Two of my projects are not shown in the Reporting DB and am getting the follwoing error

    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.. Details: id='24006'

    Do I need to delete the Orphan tasks again and have to run the complete activity??


    Thanks, Siskin.

    Wednesday, June 5, 2013 11:20 AM
  • Forcing a reporting database rebuild might help. Do an administrative backup and restore of your custom fields next time everyone is off the system.

    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG

    Friday, June 7, 2013 9:30 PM
    Moderator