none
ReportingRefresh Job continues to fail. RRS feed

  • Question

  • My Report results were starting to get out of sync with PWA results so decided to do a rebuild of the project server reporting database to see if this cleared the issue.  I've done it a couple of time and it seemed to correct the problem.  This last time I tried it it failed and continued to fail after trying a couple more times.  I keep getting this message;

    Your ReportingRefresh job failed.  Its current state is FailedNotBlocking.  It was 0% complete.  It entered the queue at 06/26/2012 17:40:34.

    To get more information about the job failure, please go to Project Web App.  Select Personal Settings from the left menu.  Then select My Queued Jobs.

    The errors returned from the queue are as follows:

     Error ID: 24023

     Error ID: 26000

    Detailed error below - send it to the administrator for more detailed troubleshooting.

    <?xml version="1.0" encoding="utf-16"?>

    <errinfo>

      <general>

        <class name="Reporting message processor failed">

          <error id="24023" name="ReportingRDBRefreshMessageFailed" uid="0878cc87-3028-45ab-9ca5-9c9b0bb20671" QueueMessageBody="One of the stages of the Refresh operation failed" Error="RDB area: Epm, error mode: ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:05:00" />

        </class>

        <class name="Queue">

          <error id="26000" name="GeneralQueueJobFailed" uid="8b0f9783-f69c-435e-bf1d-ce6391b023f7" JobUID="b5a00d49-63f4-4b4b-aef3-898a522942cf" ComputerName="APOLLO" GroupType="ReportingRefresh" MessageType="ReportRefreshMessage" MessageId="1" Stage="" />

        </class>

      </general>

    </errinfo>

    You can do the following:

    1. Try troubleshooting using the error IDs, error XML.

    2. Contact administrator with your jobID (b5a00d49-63f4-4b4b-aef3-898a522942cf) and error XML.

    I went into the ULS logs and this is what I find;

    PWA:http://apollo/PWA, ServiceApp:Project Server Service Application, User:SUN\forresto, PSI: [RDS] Start processing RDS job b5a00d49-63f4-4b4b-aef3-898a522942cf. Message type ReportRefreshMessage. Message: RDB area: Epm, error mode: ContinueOnErrors, lock RDB on errors: False, refresh sleep time: 00:05:00.

    I'm not sure what all this means so could use some help.  My OLAP cube is not updating properly and I suspect this is a result from the failed ReportingRefresh failed job.

    Thanks Forrest

    Wednesday, June 27, 2012 7:08 PM

Answers

  •  

    Yes, I am glad to hear that issue has been fixed now.

    As discussed earlier, RDB refresh is a very critical and risky task to perform in project server, if its successful then no problem but if it fails then its a big problem and this is because when we initiate RDB refresh, project server actually empty content of reporting database and start with updating each following entities

    Fiscal Periods Sync, Lookup Table Sync, Custom Field Metadata Sync, Entity User View Refresh, Resource.

    During RDB refresh you can see these jobs in the project server queue.

    Re-saving each CF and LT is another way of performing partial RDB refresh , especially when RDB db is very large in size or reporting (publish) job is throwing error related to metadata mis-match.

    I hope your doubts are clear now. If you found my responses helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer"


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    • Marked as answer by Forrest Monday, July 2, 2012 3:27 PM
    Monday, July 2, 2012 3:23 PM
    Moderator
  • Hi Hrishi,

    You will be glad to hear that after correcting the Enterprise Custom fields that were reporting errors while trying to save and Rebuilding the ReportingDB, the ReportingRefresh completed successfully with no failures.  Thanks so much for sticking with me on this.  It has been an arduous journey.  I think the field with the Switch formula was the biggest issue.  I was able to replace it with another switch formula from 'msprojectexperts'

    http://www.projectserverhelp.com/Lists/Posts/Post.aspx?List=f884b43f%2D61db%2D4c4e%2D94a3%2D38f21a8efdad&ID=27&Web=28a89920%2D6690%2D45b4%2D9132%2D339c2f40a7e7

    And accomplish basically the same thing.  Not sure why the original did not work but right now I have bigger fish to fry.

    I would appreciate any insight how you were able to determine the problem may be in the custom fields?

    Thanks again for your help.

    Forrest

    • Marked as answer by Forrest Monday, July 2, 2012 2:38 PM
    Monday, July 2, 2012 2:38 PM

All replies

  • Hi Forrest,

    Yes, you are right about the OLAP data, if reporting database is out of sync, correct information will not be displayed in OLAP cube based reports.

    Any idea at which statge RDB refresh job is failing?


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Wednesday, June 27, 2012 9:42 PM
    Moderator
  • Hi Forrest-

    It seems to be a known issue. Did you upgrade your project Server with Feb CU recently. If yes then a new bug is introduced with this CU. You can refer to the below blog by Brian SMith.

    Microsoft has released the June CU today, and it should fix the error you are reporting.

    http://blogs.msdn.com/b/brismith/archive/2012/05/23/project-server-2007-reporting-project-publish-queue-job-fails-to-complete.aspx

    Hope this helps.

    Dev


    Dev EPM Consultant

    Thursday, June 28, 2012 4:21 AM
  • Dev,

    I don’t think so this issue is similar to the issue described by Brian Smith is related 2007.

     

    Forrest,

    Correct me if I am wrong , but with reference to yesterday’s Business Intelligence report related thread, your issue is related to 2010.

    Have you verified that Reporting (Publish) job is successfully getting processed during publishing any project plan?


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 6:01 AM
    Moderator
  • Sorry, The shop had to perform some xrays close by and I had to leave quickly.

    Yes to both your questions Hrishi; We are using Enterprise PS2010 SP1 (1/6/2012)

    This is from last night.  Hope it helps?  Your other question about what stage it might be failing; I'm not sure how to determine this.  It seems to be right away because it cancels the jobs and I get an email with the error within the 1st hour of processing.

    Thursday, June 28, 2012 1:15 PM
  •  Since you are able to publish individual project plan without any problem, I don’t think you need to perform RDB refresh.

    RDB refresh is necessary when none of jobs related to Reporting database is working.

    Now, my next question is for missing information in the OLAP cube, are you able to publish those project plan successfully?

    When RDB refresh is initiated, there are 5 different jobs initiated behind the scene, if you navigate to Manage queue and add "Success" from jobs completion stats you will able to see those jobs.


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 3:33 PM
    Moderator
  • Yesterday I had to republish all my working projects the rebuild OLAP.  Then my original report connected to OLAP was correct.

    On other related forum answers I've seen it indicated that if your data was out of sync between your PWA values and your OLAP reports you should rebuild the Reporting DB so that was my initial direction.  But you are sort of saying that is not the case, correct?

    Thursday, June 28, 2012 3:46 PM
  • No, performing RDB refresh is not a bad thing, it's time consuming and critial action to perform. If you have many projects with incorrect information from the reporting database, performing RDB refresh is right thing to do.

    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 3:52 PM
    Moderator
  • So at this time should I just ignore the Failed Job for ReportRefresh?
    Thursday, June 28, 2012 3:54 PM
  • No we have to fix this issue. Since you have already published all project plan without any issue, before attempting RDB refresh, we may need to re-save each enterprise custom field and look up table. This is another way to perform partial RDb refresh.

    Open each enterprise custom field one by one and without making any changes click on save. Validate the job status before proceeding  to the next field

    Once this is done, you need to initiate RDB refresh


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 4:14 PM
    Moderator
  • Do I save both the custom fields and their associated lookup tables if they have one?
    Thursday, June 28, 2012 5:28 PM
  • Yes, each and every custom field and lookup table


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 6:32 PM
    Moderator
  • OK.  I am almost through the Custom field list and have found a few problems, which I'll list in next post and after I have finished the Lookup tables.  Appreciate the patience, thanks.
    Thursday, June 28, 2012 6:36 PM
  • OK, here goes;

    I had three custom fields that would not save due to errors, from formulas, and one field with a strange reaction to the save because of a non-existent Lookup table, but it saved.  I assume you next direction is to correct these and resave but I have some questions first.

    What is the impact to active and archived projects?

    •  After corrections to these fields that appeared to work just fine in the older version, (PS2003)?  We did migrate about a 100 projects with websites through PS2007 to PS2010 from PS2003, was probably some of the problem.
    • If it comes down to just elimininating the custom field to correct it.  There are a number of fields that were created in the first version that we ceased to use and no longer need.  Can I delete these without too many problems?

    Here are the errors I found;

    1. Custom field name: "Project Status"; error message = "An unknown error has occurred".  Formula = [Schedule - Task Status]
    2. Custom field name: "Schedule - Task Status"; error message = "This formula contains errors. Correct the formula and try again".  Formula =

    Switch([Baseline Estimated Finish]=projdatevalue("NA"),"No baseline",([% Work Complete]=0 And [Baseline Estimated Finish]<=Now()),"Start and Finish are Past Due",([% Work Complete]=0 And [Baseline Estimated Start]<=Now()),"Start is Past Due",(([% Work Complete] Between 0.001 And 99.999) And [Baseline Estimated Finish]<[Scheduled Finish]),"In Process but Tracking Late",([% Work Complete]=100 And [Scheduled Finish]>[Baseline Estimated Finish]),"Completed but Late",([% Work Complete]=100 And [Scheduled Finish]<=[Baseline Estimated Finish]),"Completed On Time",True,"In Process-On Time")

    I'm wondering if it's the Switch formula itself?  It worked fine in PS2003 and it appears to work ok in the PS2010 views of PWA so I'm not sure how to fix this.  This is probably what's causing the first field error?

     3.  Custom field name: "Status Date"; error message = "The custom field name is a reserved name".  Formula = [Status Date]

     4.  Custom field name: "Team Name" anomally - when you open the custom field to save it the Custom Attribute section showed it pointing to a lookup option but no table was listed.  I looked and there was no Team Name lookup defined below.  I switch the option to None and tried to save but got a message; "The Team Name custom field is going to be permanently mapped to the Lookup table.  This setting cannot be changed subsequently.  Are you sure you want to use the Lookup table for the Team Name field?"  I picked OK to proceed, thinking it would stay at None for the custom attribute section but instead it went right back to what it was, pointing to a blank Lookup option but it did change the Save date, which indicates it was saved.

    I don't recall creating this custom field, is it an out-of-the-box field from Microsoft?

    I'd like to get some feedback before proceeding, thanks.

    Thursday, June 28, 2012 7:52 PM
  •  

    Yes, we have to fix those problematic custom fields.

    It is not necessary that formula which is working in 2003 , may work exactly as it is in 2010, you may have to re-visit the formula to make appropriate changes

    There are certain custom fields and look up tables which are reserved. And Team name is one of them. Here is the complete list for your reference

    http://technet.microsoft.com/en-in/library/ee662499(en-us).aspx


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 9:04 PM
    Moderator
  • What about my custom fields?  Can I get rid of the ones that I don't need today?  Or will that produce other issues?  Maybe I can just leave them in PWA but eliminate them from my Enterprise Global file so they won't publish any longer?
    Thursday, June 28, 2012 9:18 PM
  • Yes, custom fields which are no longer required , just stop using it, deleting may not be the good idea.


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Thursday, June 28, 2012 9:21 PM
    Moderator
  • Hi Hrishi,

    You will be glad to hear that after correcting the Enterprise Custom fields that were reporting errors while trying to save and Rebuilding the ReportingDB, the ReportingRefresh completed successfully with no failures.  Thanks so much for sticking with me on this.  It has been an arduous journey.  I think the field with the Switch formula was the biggest issue.  I was able to replace it with another switch formula from 'msprojectexperts'

    http://www.projectserverhelp.com/Lists/Posts/Post.aspx?List=f884b43f%2D61db%2D4c4e%2D94a3%2D38f21a8efdad&ID=27&Web=28a89920%2D6690%2D45b4%2D9132%2D339c2f40a7e7

    And accomplish basically the same thing.  Not sure why the original did not work but right now I have bigger fish to fry.

    I would appreciate any insight how you were able to determine the problem may be in the custom fields?

    Thanks again for your help.

    Forrest

    • Marked as answer by Forrest Monday, July 2, 2012 2:38 PM
    Monday, July 2, 2012 2:38 PM
  •  

    Yes, I am glad to hear that issue has been fixed now.

    As discussed earlier, RDB refresh is a very critical and risky task to perform in project server, if its successful then no problem but if it fails then its a big problem and this is because when we initiate RDB refresh, project server actually empty content of reporting database and start with updating each following entities

    Fiscal Periods Sync, Lookup Table Sync, Custom Field Metadata Sync, Entity User View Refresh, Resource.

    During RDB refresh you can see these jobs in the project server queue.

    Re-saving each CF and LT is another way of performing partial RDB refresh , especially when RDB db is very large in size or reporting (publish) job is throwing error related to metadata mis-match.

    I hope your doubts are clear now. If you found my responses helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer"


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    • Marked as answer by Forrest Monday, July 2, 2012 3:27 PM
    Monday, July 2, 2012 3:23 PM
    Moderator
  • Hi Harish and all,

    This is truly helpful.

    When I saved Lookup Table, it got saved, but there was job created (either success or failure) on Project Server queue. Do you have any idea why?

    Tuesday, December 30, 2014 3:14 PM