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
}  

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.

Locale differences within WSS 2.0

‘The language is not supported on the server’ and SharePoint development.
 
I have recently been developing a C# Console Application to automate the upload process of existing documents from a folder structure into WSS 2.0. My development and testing had been going well especially considering this was my first taste of developing against SharePoint. When I ran my application on a test server the documents, and the relevant folder structures were created and uploaded successfully. The problems started to occur when I ran the application on the client’s server.
 
The application would not even create the initial site required to store the Shared Documents and just returned an error – The language is not supported on the server. Having been burned by so called ‘language’ issues before I first checked the Regional Settings for the server and they were identical to my test machine. Plan B – surf the net for a fellow techie who had encountered the same problem and solution. Although this error was well documented on the net it was more to do with the installation of SharePoint rather than developing against it. However, it did lead me down the righ path, namely then InstalledLanguages registry entry.
 
I tweaked my console application to display the list of sites within a SharePoint web and the associated Locale ID and it highlighted the reason for my error. On my test server the sites were configured with a Locale ID of 1033 (English – United States) but on the live server they were configured to use 3081 (English – Australia). The question was, how can I convince SharePoint to use the locale id of 3081.
 
The answer comes in two parts;
  1. first of all I updated the registry on the live server. The key HKEY_LOCAL_MACHINESOFTWAREMicrosoftShared ToolsWeb Server Extensions6.0InstalledLanguages already had a REG_SZ value for 1033 so I added a second one for 3081.
  2. The next task was to create the relevant folders in the SharePoint virtual directory for the site templates. To do this I made the following changes;
    • Make a copy of the C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATE1033 folder including all its contents and rename it to 3081.
    • Make a copy of the C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATEADMIN1033 folder including all it contents and rename it to 3081.
    • Make a copy of the C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATELAYOUTS1033 folder including all of its contents and rename it to 3081.
  3. Restart IIS.

This now provides SharePoint with the template structure it needs to create new sites using the Locale ID of 3081. When I ran my Console Application this time the sites were created successfully and my Shared Documents folders now contained the uploaded files.

SharePoint Services 2.0 And PDF Filters

Enabling SharePoint Services 2.0 to search PDF Files
 
There are some technological problems that take longer to resolve than others but in the end there is always an answer. Over the last couple of weeks I have been using Windows Small Business Server 2003 R2 and finding out about is many useful features. A business problem required a client of ours to be able to organise and manage a large number of documents which will be a mixture of Microsoft Office documents and Adobe Acrobat files. The cient needs to be able to search for content within these files which I knew was available as Sharepoint functionality but encountered the ‘No items were found matching your query’ when I began searching through the PDF documents.
 
I spent several hours trawling the net looking for an answer. I found several solutions, mostly applicable to WSS 3.0 or Sharepoint 2007 but non that applied to my scenario. I then came across a document from the Windows SharePoint Services 2.0 IT Documentation called Upgrade Considerations which shed light onto my problem. As part of the installation of the SBS Server I had upgraded the database used by WSS from the WMSDE to the full version of SQL Server 2005 Workgroup Edition which is supplied with SBS 2003 R2 Premium Edition. This enabled WSS to use the document indexing functionality provided by SQL Servers Full-Text Indexing service (MSFTESQL). In the section called Considerations for Upgrading from SQL Server 2000 to SQL Server 2005 there is a sub-section of Warning Descriptions detailing issues and work arounds following the upgrade. The problem was that the Full-Text Search Engine does not load third party components by default i.e. PDF’s. In order to enable the instance of MSFTESQL to load the third party filters you need to run the following commands against your Sharepoint instance;
sp_fulltext_service ‘load_os_resources’, 1
sp_fulltext_service ‘verify_signature’, 0
Once you have done that you need to rebuild your indexes by doing one of the following;
  1. Run the following command against your SQL Server SharePoint instance – EXEC sp_fulltext_catalog ‘ix_<fulltextCatalogName>’, ‘rebuild’
  2. Open SQL Server Management Studio for the SharePoint instance and expand the SQL Server SharePoint database. Under the Storage folder, expand the Full-Text Catalogs folder and then right-click the catalog and select ‘Rebuild’. Click ‘OK’ when the confirmation dialog box appears to start the rebuild.

This will enable you to search the content of existing and new PDF files within WSS 2.0. Information on upgrade the SharePoint database from WMSDE to the WorkGroup Edition of SQL Server can be found on SBS installation DVD under <dvd-drive>:PREMTECHSQLINSTALLSTEPS.HTM or here.