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 (0×80131904): 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.

Bulk Edit v1.0.4 Update

A new version of Bulk Edit is available from the App store, check your site settings for the upgrade link now to install it.

New Features

  • Project Owner Field editing support

Bug Fixes

  • Loading never finishes in some cases
  • Loading of projects is incomplete errors occur in the F12 script console

Installation

Open site contents in PWA to find the update link:

sitecontentslink

 

Finally if you use and love Bulk Edit, please rate it in the App Store!

CICONotCheckedOut queue errors when updating projects via JSOM

I’ve written many times before about working with projects using the JSOM and CSOM API’s, and this is another issue in the API that I’ve had to resolve for one of my apps (in this case Bulk Edit).

Issue

When updating built in fields (I haven’t observed this for custom fields) using the client side library (JSOM or CSOM or REST) in the normal way the following error can be reported in the queue unexpectedly:

CICONotCheckedOut: CICONotCheckedOut (10102). Details: id=’10102′ name=’CICONotCheckedOut’ uid=’a2da51ea-792b-e411-9af1-00155d908811′.

Queue: GeneralQueueJobFailed (26000) - ProjectUpdate.FailIfNotCheckedOutMessage. …

For example the following code from the MSDN article on this topic will intermittently get the above error:

// Get the target project and then check it out. The checkOut function
// returns the draft version of the project.
var project = projects.getById(targetGuid);
var draftProject = project.checkOut();

// Set the new property value and then publish the project.
// Specify "true" to also check the project in.

draftProject.set_startDate("2013-12-31 09:00:00.000");
var publishJob = draftProject.publish(true);

// Register the job that you want to run on the server and specify the
// timeout duration and callback function.
projContext.waitForQueueAsync(publishJob, 10, QueueJobSent);

This is despite the fact that we are obviously doing the check-out of the project in line 4.

Worse still:

  1. The update will still work for most fields (like Status Date but NOT for Project Owner), despite the error indicating otherwise!
  2. The error is not consistent, some updates work without an error.

Cause and Solution

Turns out the issue is one due to the asynchronous nature of the client side libraries, specifically it looks like when performing the “waitForQueueAsync”  we are actually requesting four things:

  1. Check out Project
  2. Update project value (start date above)
  3. Publish Project
  4. Check project back in

However it seems that steps 2 and 4 don’t quite run in the correct order!  Changing line 10 as follows to NOT check-in after publishing results in a successful update and no error:

var publishJob = draftProject.publish(false);

Then we need to add a separate checkIn() call AFTER the completion of the publish (IE in the callback function ‘QueueJobSent‘ above) and then call waitForQueueAsync again.

Looks like a bug although perhaps not as it is important to keep in mind that the queued async jobs are not guaranteed to be done in the correct order although clearly it usually happens in the order expected.

 

BTW, Yes expect an update for Bulk Edit supporting more built-in fields soon!

Problems installing Apps in SharePoint farm

Recently while working with a colleague we came across a few easy to miss issues when configuring the App Management Service in a SharePoint farm and while troubleshooting those issues found a lack of online resources on what turned out to be super-simple issues to resolve. (Hindsight is great)

Issue 1: ‘Let’s try this again’ error installing from the App Store

After installing and configuring the services, app domain and catalog as required, we could now open the SharePoint App Store, and choose an app to install, however the following message was displayed and the installation of any App would never begin.

Capture

Error text: Everything is fine, but we had a small problem getting your license. Please go back to the SharePoint Store to get this app again and you won’t be charged for it.

Everything’s fine hey? Looking at the ULS revealed that this was caused by security:

w3wp.exe (0x0CA0) 0x1DF4 SharePoint Foundation App Marketplace High An exception was thrown while trying to import the app license. AppName:Bulk Edit; Exception:System.UnauthorizedAccessException: You don’t have the right to perform this operation. at Microsoft.SharePoint.SPAppLicenseManager.ImportLicense …
w3wp.exe (0x0CA0) 0x1DF4 SharePoint Foundation App Marketplace High Post app license acquisition did not result in a successful license import. HugState:Retry

But how can this be, we’re testing using the Farm Admin account and so we have all permissions! It turns out, that is actually the problem! While I can’t say specifically where / what setting causes this, in a typical SharePoint 2013 install the Farm Admin (/ Installer account) is intentionally restricted in certain ways, my guess is something to do with local computer policies or such.

Solution

Login using a user account (who can be Site Collection Admin and all that)!

Capture2

Yay my favourite app!

Issue 2: App installs without issue but does not work

Now we have our first app installed, however running it doesn’t quite work out. Unfortunately this time we get very little indication of the problem, in fact with Bulk Edit the app will start and look good, except nothing works! The buttons do nothing, and no data is displayed! :(

Capture3

Similar issues happen with other apps installed and without looking at the F12 browser debug console the only indication that there is a problem is the top right hand corner site icon image which is just a little ‘x’.

If you do debug you will see an error like this:

SCRIPT5009: ‘RegisterSod’ is undefined

File: Default.aspx, Line: 14, Column: 32

Basically that tells me as a developer that something is seriously wrong in the farm (SP.js is missing which the whole JSOM api requires)!

Cause & Solution

Fortunately the solution was simple: SP.js was actually missing! Well not exactly, as it turns out while we had provisioned a single PWA site collection at /PWA in our Web App, we had not actually created a Default Root site collection at ‘/’. This configuration actually can break lots of things including SSRS and so this comes as no surprise really.

Simply creating a root site collection (using Blank or any template) fixed the issue and now all apps work as normal.

 

Other potential issues

While possible the topic of another rather long post, here’s a list of other things to check if you get permissions issues trying to install apps (sorry this is from memory so pls comment below if you think any of this has changed):

 

Hope that helps someone else out there!

SSRS Report export to PDF removes spaces from text

I saw this issue when working with an old colleague Hammad Arif earlier this year and he came up with a solution written up here that worked nicely at the time, however I’ve come across this again and this time it proved more of a challenge.

Problem

When exporting to PDF Reporting Services reports containing multi-line text from a SQL datasource the first line of text loses all spaces between words, see screenshot taken from a PDF file:

ssrspdfexport

 

Nope that’s not one particularly long German conjugation, that is the problem I’m talking about! It only seems to affect the first line of each multi-line based custom field value in the SSRS dataset and additionally when looking at the data in SQL or in the HTML source there is nothing different visible! However oddly on the affected projects a single odd character (&#8203;) does exist before the html <span>.

 

Resolution

After much head scratching it took a call to Microsoft to identify that hidden away in the database tables are some characters, specifically HEX ’0B 20′ which turns out to be the Zero Width Space or in HTML (&#8203;), the same character you can see in the HTML. Now this makes sense as despite these being invisible in the SQL tables using a SELECT statement, they still exist and cause these symptoms in the PDF but not when viewed on the Web or exported to Word or other formats.

Fortunately the resolution for the report is nice and simple;

SELECT REPLACE(Problemstellung, NCHAR(8203), '') AS Problemstellung

Simply wrap each multi-line text field affected with the SQL REPLACE command removing the character completely!

 

To answer the question of where they came from, that is one I leave to the Microsoft escalation engineers. :)

HTH,

Nearbaseline redesign and site overhaul

NearbaselineIt’s something I’ve wanted to do for sometime now, but finally found the time and resources to do it! Have a look around the new modern design (I guess that’s not actually modern-ui, meh?) and please let me know if you find something that doesn’t work! :)

Martin.