Month: December 2012

SharePoint Management Shell (PowerShell) stops working after Windows Update installs Windows Management Framework 3.0

I recently logged into a SharePoint 2010 App Server (Server 2008 R2 SP1) and started SharePoint Management Shell. I was greeted with the familiar “The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered”, and were also unable to execute any SharePoint PowerShell commands. The ventured reader will recognize this error, it’s the one that is displayed when the logged in account does not have permissions. The fix for that is quite easy, add the user as Shell Admin using Add-SPShellAdmin (using an account with appropriate permissions, of course).

In this case, however, this did not make any sense. I had previously used the SharePoint 2010 Management Shell on this server and, just to confirm, one of the local IT Administrator were also granted this warning message and were unable to run any SharePoint PowerShell commands.

Further investigation showed that the server had been patched using Windows Update earlier the same day. One of the updates that had been installed was KB5206143, Windows Management Framework 3.0 for Windows 7 SP1 and Windows Server 2008 R2 SP1, which among other features installs PowerShell 3.0. The update was published (published 11. Nov. 2012), As you might know, SharePoint 2010 does not like PowerShell 3.0 very much, yet… This is actually an issue even in Server 2012.

A bug has been raised for PowerShell 3.0, but closed by Microsoft with the message: image

Perhaps a fix is coming in a cumulative update soon? Until then, the workaround is quite simple: Start PowerShell with the parameter “-Version 2.0” or just “-v 2” and then add the SharePoint Snap-In using “Add-PSSnapIn Microsoft.SharePoint.PowerShell”.

I haven’t tried to uninstall the Windows Management Framework 3.0, but I don’t see why that wouldn’t work as well.

If Google brought you here, I hope I have been of help.

Querying for current user’s items using the Content by Query Web Part’s QueryOverride property

We have all learned to love and adore the Content by Query Web Part (CQWP) for showing content from our site collection without having to put our hardcore developer gloves on. It’s great for easily showing content of a certain kind, from a certain site or belonging to a certain user. We’re usually configuring the web part using the three filter properties of the web part in order to show the content we’re looking for.  Unfortunately, sometimes three filter properties are not enough.

image

Say we have several lists of a certain content type with the three columns Status, Responsible and Assigned. We want to show items from this list where status is equal to ‘Open’, and the current user is either in the Responsible-column or in the Assigned-column. We are unable to use the web part’s properties to filter the content, because the three filter properties of the web part is not enough for us to formulate the query. We want items where Status=Open AND (Responsible=Current User OR Assigned=Current User). Unfortunately the filters will be interpreted as (Status=Open AND Responsible=Current User OR Assigned=Current User). This will give us all items where Status is Open and Responsible is Current User, but it will also give us all items where Assigned is Current User, independent on the Status! We don’t have any possibility to change which expression is being interpreted first. This leads us to the QueryOverride property. (Never mind that the MSDN documentation is awful and incorrect: “Gets or sets the list field name used to filter the set of list items.”?)

The QueryOverride property allows us to write good, old CAML. You can try writing the CAML-queries by hand, but I recommend checking out CAML Designer, SharePoint CAML Query Helper or some other tool to help you write those hairy CAML-queries.

Now to the point of this post. Using tools like the above or by looking up resources in various tech forums, the CAML used to filter items by the current user leads us to the following CAML:

<Where>
   <Eq>
      <FieldRef Name='Responsible' /> /*Some will add LookupId=’TRUE’ here. */
      <Value Type='Integer'>
         <UserID Type='Integer' />
      </Value>
   </Eq>
</Where>

This looks great, right? Well, it’s actually not working in the QueryOverride-property (the generated CAML is working in the CAML-tools and data queries). After a lot of experimenting, I found out that you cannot look up the Integer value of the user when filtering on the current user! Fortunately, only minor changes are necessary to get it to work. Change Type from Integer to User, and remove the type attribute from the UserID tag. The working CAML should look like the following:

<Where>
   <Eq>
      <FieldRef Name='Responsible' />
      <Value Type=’User’>
         <UserID />
      </Value>
   </Eq>
</Where>

And the full CAML to find all items with status ‘Open’ and with the current logged in user as assigned or responsible?

<Where>
  <And>
    <Eq>
      <FieldRef Name='Status' />
      <Value Type='Choice'>Open</Value>
    </Eq>
    <Or>
      <Eq>
        <FieldRef Name='Responsible' />
        <Value Type='User'>
          <UserID/>
        </Value>
      </Eq>
      <Eq>
        <FieldRef Name='Assigned' />
        <Value Type='User'>
          <UserID/>
        </Value>
      </Eq>
    </Or>
  </And>
</Where>

A final note on how to use the QueryOverride property: In your .webpart file you should wrap the CAML in the CDATA tag, and you should remove spaces and line breaks. If you’re deploying your web part to a page through a module, you need to HTML-encode the CAML. The easiest way I have found to do this is to deploy the .webpart file with the Queryoverride property set, and then add the webpart to a page. You should then be able to export it, and the QueryOverride-property will have it’s content encoded, which you can reuse.