OData error processing this request
I came across this while working with a customer and quickly found that the error triggers a known issue in my Bulk Edit app as well which was causing some unexpected errors using the app for some people, however this is a broader issue as it causes basic OData feeds to fail completely with the following message: Error message accessing /_api/ProjectData/Projects
<?xml version="1.0" encoding="UTF-8"?> <m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <m:code/> <m:message xml:lang="en-US">An error occurred while processing this request.</m:message> </m:error>
The problem can happen both on-prem or online but if you have access to the ULS logs you will see the following log entries (showing only the interesting bits):
Project Server Database agxfb Exception [bucketHash:7C60C52B] SqlException occurred in DAL (ProjectWebApp@NB): Class 16, state 1, line 1, number 8156, procedure , error code -2146232060, exception: System.Data.SqlClient.SqlException (0x80131904): The column ‘Campaignstatus’ was specified multiple times for ‘OdataSelect’. Project Server OData abljj High [Forced due to logging gap, Original Level: Verbose] PWA:http://nb/PWA, ServiceApp:Project Server Service Application, User:i:0#.w|blah\ml, PSI: Exception encountered when trying to instantiante the OdataResultItemCollection
As error logs go that is a pretty helpful one!
Cause – Duplicate Custom Fields
The issue is caused by the Enterprise Custom Field configuration of the Project Server, specifically to looks like there are some duplicate named fields (Campaignstatus in my error above), now of course that is of course not possible! However when you look at the OData feed you can see that a few things happen:
- Spaces are removed from field names; so e.g. Campaign Status becomes CampaignStatus, etc.
- Duplicate named fields (after space removal) are numbered, so for example you may see CampaignStatus1.
However there is a bug, OData is case-sensitive while SQL is not. So in my example above it seems that there are two fields the same name but with different cases, specifically I have the following two fields configured: Campaign Status and Campaignstatus. In my case if the second field was named CampaignStatus then this issue would not occur as it would have a number appended in the feed! (While this might sound a little esoteric actually I’ve already seen this a couple of times in German PWA instances where words are typically conjugated, etc)
Solution
Simple, either;
- Rename the aforementioned field to something completely different (e.g.; Campaignstatus2), or
- Recreate the field with different Case, e.g. in my example if I recreated the field Campaignstatus as CampaignStatus.
Note; renaming a field to a different case does not work(!), you need to recreate the field in order to regenerate the correct ‘cased’ Odata name.
Hopefully this bug will be fixed in a future service pack, but for now in Bulk Edit at least I have implemented a workaround anyway.
