SharePoint Online List View Thresholds

Running CAML queries against List Views with a large number of items (> 5000) can fail with the following error.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

To overcome this problem we have to ensure our query meets these requirements when running against SharePoint Online.

  • Run the query in a loop a number of times using the ListItemCollectionPosition to keep track of the looping process.
  • Ensure that the columns specified in any Query Where clause and the OrderBy are already indexed on the list view. If you include columns in either of these parts of the query that are not indexed then you will still get the same error.
  • Ensure the Query AllowIncrementalResults is set to true
  • If you want to restrict the Query to a particular folder in the List View set the FolderServerRelativeUrl property.

I used PowerShell to test out my CAML query.

#Load SharePoint CSOM Assemblies
$folder = "C:\Nuget\packages\Microsoft.SharePointOnline.CSOM.16.1.8412.1200\lib\net45"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.dll"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.Runtime.dll"
   
#Variables for Processing
$SiteUrl = "https://mysharepoint.sharepoint.com/sites/mysite"
$ListName="MyList"
 
$UserName="me@mysharepoint.onmicrosoft.com"
$Password ="nottellingyou"
  
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
  
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials

try {
   
    #Get the List
    $List = $Context.web.Lists.GetByTitle($ListName)

    $Context.Load($List)
    $Context.ExecuteQuery()

    $position = $null

    $allItems = @()

    Do {
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery;
        # Define the starting position
        $Query.ListItemCollectionPosition = $position

        $Query.ViewXml = "<View Scope='RecursiveAll'>  
                            <Query> 
                                <Where>
                                    <And>
                                        <And>
                                            <And>
                                                <Eq><FieldRef Name='ReportName' /><Value Type='Text'>Monthly Report</Value></Eq>
                                                <Eq><FieldRef Name='Category' /><Value Type='TaxonomyFieldType'>Statement</Value></Eq>
                                            </And>
                                            <Eq><FieldRef Name='ProductLine' /><Value Type='TaxonomyFieldType'>Credits</Value></Eq>
                                        </And>
                                        <Eq><FieldRef Name='ContentType' /><Value Type='Computed'>Report</Value></Eq>
                                    </And>
                                </Where>
                            </Query> 
                        </View>"

        # Define a folder for the starting point of the recursive search
        $Query.FolderServerRelativeUrl = "/sites/MySite/MyList/Folder1"
        $Query.AllowIncrementalResults = $true

        $ListItems = $List.GetItems($Query) 
        $Context.Load($ListItems)

        $Context.ExecuteQuery()    
    
        # Increment the next position for the search
        $position = $ListItems.ListItemCollectionPosition
    
        $allItems += $ListItems
    }
    Until($position -eq $null)
}
Catch {
    Write-Error $_.Exception.Message
    Break
} 


write-host "Total Number of List Items found:"$allItems.Count
 
#Loop through each item
$allItems | ForEach-Object {
    #Get the Title field value

    $Context.Load( $_.File)
    $Context.Load( $_.File.ListItemAllFields)
    $Context.ExecuteQuery()
    write-host $_.File.Name
}  
Advertisements

CSOM – ContentType and CAML Queries

To retrieve the ContentType of a ListItem from the ListItemCollection using the GetItems method, there are two approaches that work.

  1. If you are not specifying ViewFields in your CAML query then in your Load method you need to Include the ContentType. e.g.

clientContext.Load(listItems, items => items.Include(item => item.ContentType));

  1. If you CAML query includes ViewFields then two modifications are required. The ViewFields needs to include the ContentTypeId field and the Load method needs to include the ContentType. e.g.
<ViewFields><FieldRef Name='ContentTypeId' /></ViewFields>

clientContext.Load(listItems, items => items.Include(item => item.ContentType));

SharePoint Client – Folder and Files

If you know the relative Url of the folder within SharePoint there is a straight forward way to access the files within that folder.

using Microsoft.SharePoint.Client;
...
using (ClientContext clientContext = new ClientContext(&amp;quot;https://something.sharepoint.com/sites/mysite/&amp;quot;))
{
    Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(&amp;quot;/sites/mysite/root_folder/sub_folder&amp;quot;);

    // Load the Folder and Files into the ClientContext so we can access them
    clientContext.Load(folder);
    clientContext.Load(folder.Files);
    clientContext.ExecuteQuery();

    foreach (var item in folder.Files)
    {
        // Load each file into the ClientContext to access the file information
        context.Load(item);
        context.ExecuteQuery();
    }
}

SharePoint Deployment – Access is denied!

I got the following error while trying to upgrade a site feature in SharePoint:

The copying of this file failed: Feature.xml. 
Access to the path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\
Template\Features\<MyFeature>\Feature.xml' is denied.



I hadn’t changed any of the SP settings and the process for doing the deployment hadn’t changed so what was SP getting upset about now??? Taking a look at the directory where the Feature.xml was supposed to be installed provided me with the answer. For some reason, yet to be explained by computer science, the directory was left in a state of limbo. I couldn’t open it to view the content, see the security settings or rename/delete it. First of all I tried resetting IIS to see if that would release its grip on this defunct directory but this gave me no further progress. In the end I restarted the machine, the phantom directory disappeared and I was able to continue on with my deployment process as normal.

 

Feature activation error

The element of type ‘Module’ for feature ‘<feature name>‘ (id: 03fffd3f-5b80-4507-87ed-ab8797883b04) threw an exception during activation: Cannot complete this action.  Please try again.

Whilst trying to activate a new feature I had deployed to SharePoint I got this error (above). My feature worked fine during development but the activation just kept throwing an error. The error wasn’t very helpful either and the stack trace in the Unified Logging Service didn’t really give me any more clues.

My Elements.xml file had two Modules specified. One for a web page and one for the web parts that were going to sit on the web page. I tinkered with the Elements.xml file and commented out the page module to see if that was the problem. Low and behold when I tried the activation this time the process completed successfully. Somewhere in the web page module there was a conflict!

<Module Name="WebPages" Path="" Url="" RootWebOnly="True">
 <File Url="MyPage.aspx" Type="Ghostable" IgnoreIfAlreadyExists="True">
 <Property Name="Title" Value="MyPage" />
 <NavBarPage Name="MyPage" ID="1003" Position="End" />
 </File>
 </Module>

It turns out that the problem was the ID attribute value for the NavBarPage element. This feature was being inserted into a site with an existing entry in the navigation area called ‘MyPage’ with an ID of 1002. The solution was to got to the Top Link Bar option under the Site Settings and remove the unnecessary link.

Having done that and changed the ID value to match the existing value I was able to successfully activate the feature.

Redundant MOSS Features

Finding & removing SharePoint features.
I had been trying to upgrade an existing solution using STSADM as it contained some additional features. Unfortunately, the command was giving me the following error:
The solution can not be deployed.  Directory “<feature name>” associated with feature ‘351cc41f-19b9-48f2-8654-7f29da7b89b2’ in the solution is used by feature ‘5cb0e7cd-75ed-4faa-9cec-914b01338a45’ installed in the farm. All features must have unique directories to avoid overwriting files.
I needed a way to find out what this existing feature {5cb0e7cd-75ed-4faa-9cec-914b01338a45} was and where in the farm it was being used. After the usual search for enlightenment I found the FeatureAdmin tool to help me identify the location of this feature. In this case, it happened to be redundant feature that was not being used anywhere on the farm but had been left hanging around. The FeatureAdmin tool was able to uninstall the feature definition and allow me to perform the upgrade successfully.

I have persmission to do this…trust me!

Prompted for cedentials when accessing SharePoint library documents
 
At our office we have a number of MOSS 2007 document libraries. One of the issues I found with Vista is that it would constantly display the prompt for my credentials when I was trying to view any of these documents. I had already configured my Security Zones settings so that access to the document library was done through the Local Intranet zone including setting the automatic logon. Other people in the office using Windows XP were not experiencing the same issue.
 
After a few minutes surfing I came accross this Knowledge Base article that resolved my problem – http://support.microsoft.com/kb/943280 . The article explains the reasons for the problem so I won’t repeat it here however there are a couple of points worth highlighting;
  • If you have already installed Windows Vista SP1, which I did, then you do not need to download the hotfix, just apply the changes to the registry.
  • Once I had made the change to your registry I needed to reboot my machine in order for the change to be affective.

Needless to say, my issue was resolved and I am no longer getting prompted to provide my credentials when I access documents stored in our SharePoint document libraries.