Fetching Resources from Project Server

All replies

• 

  

  0

  Sign in to vote

  Hi,

  The fact that the REST endpoing (.../_api/ProjectServer/...) fails indicates that there is something wrong in the data being fetched.

  In this case as you are looking at Enterprise Resources and the error mentions custom fields so what I suggest you do is the try the following

  1. From MS Project client (with latest service packs and so installed) open and check out all resources, then resave and checkin. Possibly even try making a change to to a field to ensure that all resources are updated.
  2. If that doesnt fix it then you might need to open each resource custom field from Server Settings - Enterprise custom fields, open and save them.

  Clearly there is some corruption that needs to be fixed before you will be able to do much at all.

  HTH,

  ---

  Martin Laukkanen
  Nearbaseline blog - nearbaseline.com/blog
  Bulk Edit and other Apps - nearbaseline.com/apps

  Wednesday, August 26, 2015 9:59 AM
• 

  

  0

  Sign in to vote

  Hi ,

  I am unable to find the root cause of the issue .but I have figured other way of getting resource data.

   User NewProjectOwner = projContext.Web.SiteUsers.GetByLoginName(userLoginname);
                         projContext.Load(NewProjectOwner);
                         projContext.ExecuteQuery();
                         DraftProject draftProject = projContext.Projects.GetByGuid(pubProj.Id).CheckOut();
                         draftProject.Owner = NewProjectOwner;   

  Above is the code I used to get resource.

  Thanks

  Bhism

  • Marked as answer by Bhism Narayan Monday, October 5, 2015 5:30 AM

  Monday, October 5, 2015 5:30 AM
• 

  

  0

  Sign in to vote

  I ran into this issue also and wasted two weeks getting to the root of it. For me, it was not an enterprise resource but a project. However, the underlying cause is likely the same as yours: There is a custom field using a lookup table where the value no longer exists in the lookup table. Incredibly, Project Server (2013,2010 - don't know about 2016) will let you do this as there is absolutely zero referential integrity enforced.

  Repro Steps

  1. Create a custom field that is a lookup of type text, called “myfield”
  2. Create a lookup table called “mylookup”:
     1. Add two values: “foo”, “bar”
  3. Create an enterprise project: “test”
  4. Checkout project “test”
  5. Edit “myfield”
     1. Select value “foo”
  6. Save, close (check-in/publish)
  7. Edit “mylookup”
     1. Remove value “foo”
     2. Save “mylookup”

  Now, any attempts to retrieve the project “test” individually or as part of a larger collection - including custom fields - using CSOM will cause the request to fail with “Unknown Error.” Here's how I can invoke the issue in powershell (for example - obviously C# will fail too)

  [reflection.assembly]::LoadWithPartialName("microsoft.projectserver.client")
  $c = new-object microsoft.projectserver.client.projectcontext "http://server/pwa"
  $c.load($c.projects)
  $c.ExecuteQuery()
  $pcf = $c.projects[0].IncludeCustomFields
  $c.load($pcf)
  $c.ExecuteQuery() # boom: 

  Exception calling "ExecuteQuery" with "0" argument(s): "Unknown Error"

  The ULS log looks like this:

  01/04/2017 08:45:14.85 w3wp.exe (0x2A34)                       0x31DC SharePoint Foundation                 CSOM                                          ahjq1     High       Exception occured in scope Microsoft.ProjectServer.PublishedProject._SerializeToJson. Exception=System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.ProjectServer.CustomFieldValueBase.<>c__DisplayClass3.<get_value>b__2(Guid id)     at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()     at System.Linq.Buffer`1..ctor(IEnumerable`1 source)     at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)     at Microsoft.ProjectServer.CustomFieldValueBase.get_Value()     at Microsoft.ProjectServer.Project.GetCustomFieldValue(String fieldName)     at Microsoft.SharePoint.Client.ServerStub.WriteAsJson(JsonWriter writer, Object obj, ClientObjectQuery objectQuery, ProxyContext proxyContext)     at Microsoft.SharePoint.Client.Se...         dbc1c79d-b156-2021-ebb4-b6a469f7f085
  </get_value>

  We're about to open a ticket with premier support on this. For your own information, you can "fix" this by running the following query directly on the PS database, but obviously TAKE A BACKUP FIRST.

  delete from  pub.MSP_PROJ_CUSTOM_FIELD_VALUES where CODE_VALUE not in (select lt_struct_uid from pub.MSP_LOOKUP_TABLE_VALUES)

  Conclusion

  • The Web UI and remote interfaces (CSOM, PSI) permits removal of lookup values from lookup tables that are still referenced by lookup-based custom fields.
  • Orphaned lookup values cause the CSOM object model to fail catastrophically.

  While it is technically possible to enforce referential integrity at the application level when using the CSOM exclusively, it is entirely unreasonable to expect Project Server administrators to validate the use of any given lookup value across possibly hundreds of projects  – by hand – before modifying a lookup table.
  
  Clearly, Project Server either needs to enforce referential integrity, or allow retrieval of lookup-based custom fields that have orphaned values. 
  

  • Edited by Oisín Grehan Friday, January 6, 2017 7:24 PM typos
  • Proposed as answer by Oisín Grehan Friday, January 6, 2017 7:27 PM

  Friday, January 6, 2017 7:07 PM
• 

  

  0

  Sign in to vote

  I was able to identify then (manually) fix faulty resource custom fields by using following query, deriving from the unsupported "fix" query requiring direct database access.

  ----------------------------------------------------------------------------
  -- Identification of lookup-based custom fields that have orphaned values --
  ----------------------------------------------------------------------------
  
  SELECT res.[RES_NAME] as ResourceName, cfv.[MD_PROP_NAME] as CustomFieldName
  FROM pub.[MSP_RES_CUSTOM_FIELD_VALUES] rcf WITH (NOLOCK)
  INNER JOIN pub.[MSP_RESOURCES] res WITH (NOLOCK) ON rcf.[RES_UID] = res.[RES_UID]
  INNER JOIN pub.[MSP_CUSTOM_FIELDS] cfv WITH (NOLOCK) ON cfv.[MD_PROP_UID] = rcf.[MD_PROP_UID]
  WHERE rcf.[CODE_VALUE] NOT IN (SELECT [lt_struct_uid] FROM pub.[MSP_LOOKUP_TABLE_VALUES] WITH (NOLOCK))
  ORDER BY ResourceName, CustomFieldName

  
  
  

  • Edited by Olivier DHEERE Thursday, October 5, 2017 8:25 AM spelling

  Thursday, October 5, 2017 8:17 AM