Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Enterprise Custom field not shown in reporting db view RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    We've added several Enterprise custom fields.  Until recently, they have always been added to the reporting database (MSP_EpmProject_UserView).  For several days now, the new fields are no longer automatically being added to the user view.

    Have we hit some sort of limit.  Is there a maximum number of fields custom fields that can be added to EMP 2010?

    Thanks

    Danny

    Friday, October 26, 2012 8:48 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for all the tips.  It turns out that the MSP_Epm_GenerateSqlQueryCustomFieldColumns procedure is the culprit.  Here are the offending lines of code :

    IF @TablesCount + @CountOfNewJoins > 250

    BEGIN

      SET @ViewWasTruncated = 1

      GOTO EndOfLoop

    END

    To get around this limitation, I created a copy of that procedure (don’t want to mess with the original code) in which I commented out the validation of the number of joins.

    • Marked as answer by dmj001 Monday, November 5, 2012 2:53 PM
    Monday, November 5, 2012 2:53 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Is the OLAP build running succesfully? 

    In the OLAP cube configuration, did somebody remove them from OLAP?

    How many Enterprise Project Codes are you currently using? 

    Cheers!

    Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA, MCC 2011, MCC2012
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, October 26, 2012 10:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Check in the project queue if the "Reporting (Project Publish)" is failing?

    Sunday, October 28, 2012 10:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    We don't have much to go on.  There's no smoking gun (no error messages in the log file).  We just seem to be stuck at 273 custom fields.
    Monday, October 29, 2012 5:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, you are running into a SQL limitation of 255 joins. Since each custom field adds a join, the view generation stops adding custom fields once you go past that number.  I think the actual limit of custom fields is about 240 or so, as there are some other joins already in place.

    Thanks!

    Treb Gatte | @tgatte | http://AboutMSProject.com


    Monday, October 29, 2012 7:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That seems to be the issue.  The view currently contains 248 joins.  Would you happen to know where that limitation is documented.  I can't see it mentioned here : http://msdn.microsoft.com/en-us/library/ms143432.aspx

    Thanks

    Danny

    Tuesday, October 30, 2012 8:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Look here under Table Sources. http://msdn.microsoft.com/en-us/library/ms177634.aspx

    Treb Gatte | @tgatte | http://AboutMSProject.com

    Wednesday, October 31, 2012 12:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Danny,

    I have exactly the same experience. (CU April 2012, installed in june 2012).

    In the MSP_EpmProject_UserView, I see several Enterprise Custom fields I created in the environment when CU Feb 2012 was installed.

    At this moment, when I create an  Enterprise field on project level it is not apearing in the reporting database.

    I already tried to solve the problem by regenerating the reporting database, but the fields are still missing...

    Regards
    Wim

    Wednesday, October 31, 2012 9:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Wim,

    since you are asking in this threat, I assume you are not running beyond limitation of number of fields?
    Just an idea? Are the missing fields linked to LookUp tables and allow multi-selection? With this setting, they will not show up in *_UserViews.

    Regards
    Barbara

    Wednesday, October 31, 2012 9:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for all the tips.  It turns out that the MSP_Epm_GenerateSqlQueryCustomFieldColumns procedure is the culprit.  Here are the offending lines of code :

    IF @TablesCount + @CountOfNewJoins > 250

    BEGIN

      SET @ViewWasTruncated = 1

      GOTO EndOfLoop

    END

    To get around this limitation, I created a copy of that procedure (don’t want to mess with the original code) in which I commented out the validation of the number of joins.

    • Marked as answer by dmj001 Monday, November 5, 2012 2:53 PM
    Monday, November 5, 2012 2:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Barbara,

    The field I am talking about is a simple text field on project level, not using a lookup table. I have only 10 custom fields defined, so it cannot be a matter of limitations of dynamic joins...

    I did the experiment on identical installations (DEV, Test, Prod), only the hardware is different in terms of redundancy for this environments.

    So the mentioned solution is not relevant for me ... :(

    Regards
    Wim


    Monday, November 5, 2012 3:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Problem Solved,

    On SQL Server, there was a replication mechanism set up.

    Project Server tries to drop and recreate the user views when adding or removing Custom Fields. This update mechanism towards the reporting tables cannot succeed because the replication-configuration prevents SQLServer from dropping the views!

    So the views remain in the old status (no fields are added or removed).

    Regards
    Wim

    • Proposed as answer by FeytensJ Tuesday, November 6, 2012 12:32 PM
    Tuesday, November 6, 2012 10:44 AM