4

I have an issue, I have a document library with 1,00,000 files and folders,

I want to retrieve all using client object model, when i try to retrieve with "Recursive ALL" it is coming with all records but when i put the CAML query it is throwing error "Error Message the attempted operation is prohibited because it exceeds the threshold limit enforced by the administrator" -

I understand that this is related to threshold settings but i don't want to do any changes to existing threshold settings but i want to retrieve all the records with powershell script and client side object model with caml query

3 Answers 3

2

We need to use paging to fetch data from such a large list.

For that we use ListItemCollectionPosition.

Sample code would be as below using CSOM:

ClientContext clientContext = new ClientContext("Test");
List list = clientContext.Web.Lists.GetByTitle("CustomList");
ListItemCollectionPosition position = null;
do
{
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ListItemCollectionPosition = position;
    camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
                <QueryOptions><ViewAttributes Scope='Recursive'/></QueryOptions>
                <RowLimit>1000</RowLimit>
                <Query>
                    <Where>
                        <Eq>
                            <FieldRef Name='Title'/>
                            <Value Type='Text'>Test</Value>
                        </Eq>
                    </Where>
                </Query>
            </View>";
    ListItemCollection listItems = list.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
    position = listItems.ListItemCollectionPosition;
    foreach (ListItem listItem in listItems)
        string itemTitle = Convert.ToString(listItem["Title"]);
}
while(position != null)

Sample code would be as below using powershell:

$list = $ctx.Web.Lists.GetByTitle("CustomList")
$ctx.Load($list)
$ctx.ExecuteQuery()
## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@
## Page Position
$position = $null

## All Items
$allItems = @()
Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $camlQuery.ViewXml = $qCommand
 ## Executing the query
    $currentCollection = $list.GetItems($camlQuery)
    $ctx.Load($currentCollection)
    $ctx.ExecuteQuery()

 ## Getting the position of the previous page
    $position = $currentCollection.ListItemCollectionPosition

 # Adding current collection to the allItems collection
    $allItems += $currentCollection
}
# the position of the last page will be Null
Until($position -eq $null) 

Powershell reference - Get All Items in 5000+ large list with CSOM in PowerShell

2
  • tried when we use where condition(caml) gving same error...! Jan 24, 2017 at 13:06
  • Is the where column indexed ? First check with items like ID < 3000 Jan 24, 2017 at 13:09
2
var context, 
    web, 
    spItems, 
    position, 
    nextPagingInfo, 
    previousPagingInfo, 
    listName = 'ContactsList', 
    pageIndex = 1, // default page index value 
    pageSize = 4, // default page size value 
    list, 
    camlQuery; 

// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model  
$(document).ready(function () { 
    context = SP.ClientContext.get_current(); 
    list = context.get_web().get_lists().getByTitle(listName); 
    camlQuery = new SP.CamlQuery(); 

    $("#btnNext").click(function () { 
        pageIndex = pageIndex + 1; 
        if (nextPagingInfo) { 
            position = new SP.ListItemCollectionPosition(); 
            position.set_pagingInfo(nextPagingInfo); 
        } 
        else { 
            position = null; 
        } 

        GetListItems(); 
    }); 

    $("#btnBack").click(function () { 
        pageIndex = pageIndex - 1; 
        position = new SP.ListItemCollectionPosition(); 
        position.set_pagingInfo(previousPagingInfo); 
        GetListItems(); 
    }); 

    GetListItems(); 
}); 

function GetListItems() { 
    //Set the next or back list items collection position 
    //First time the position will be null 
    camlQuery.set_listItemCollectionPosition(position); 

    // Create a CAML view that retrieves all contacts items  with assigne RowLimit value to the query 
    camlQuery.set_viewXml("<View>" + 
                              "<ViewFields>" + 
                                     "<FieldRef Name='FirstName'/>" + 
                                     "<FieldRef Name='Title'/>" + 
                                     "<FieldRef Name='Company'/>" + 
                                "</ViewFields>" + 
                             "<RowLimit>" + pageSize + "</RowLimit></View>"); 

    spItems = list.getItems(camlQuery); 

    context.load(spItems); 
    context.executeQueryAsync( 
            Function.createDelegate(this, onSuccess), 
            Function.createDelegate(this, onFail) 
        ); 
} 

// This function is executed if the above OM call is successful 
// This function render the returns items to html table 
function onSuccess() { 

    var listEnumerator = spItems.getEnumerator(); 
    var items = []; 
    var item; 

    while (listEnumerator.moveNext()) { 
        item = listEnumerator.get_current(); 
        items.push("<td>" + item.get_item('FirstName') + "</td><td>" + item.get_item('Title') + "</td><td>" + item.get_item('Company') + "</td>"); 
    } 

    var content = "<table><tr><th>First Name</th><th>Last Name</th><th>Company</th></tr><tr>" 
                + items.join("</tr><tr>") + "</tr></table>"; 
    $('#content').html(content); 

    managePagerControl(); 
} 

function managePagerControl() { 

    if (spItems.get_listItemCollectionPosition()) { 
        nextPagingInfo = spItems.get_listItemCollectionPosition().get_pagingInfo(); 
    } else { 
        nextPagingInfo = null; 
    } 

    //The following code line shall add page information between the next and back buttons e.g. 1-4

    $("#pageInfo").html((((pageIndex - 1) * pageSize) + 1) + " - " + ((pageIndex * pageSize) - (pageSize - spItems.get_count()))); 

    previousPagingInfo = "PagedPrev=TRUE&Paged=TRUE&p_ID=" + spItems.itemAt(0).get_item('ID'); 

    if (pageIndex <= 1) { 
        $("#btnBack").attr('disabled', 'disabled'); 
    } 
    else { 
        $("#btnBack").removeAttr('disabled'); 
    } 

    if (nextPagingInfo) { 
        $("#btnNext").removeAttr('disabled'); 
    } 
    else { 
        $("#btnNext").attr('disabled', 'disabled'); 
    } 

}

Try this code, it might be helpful. Pagination is using SP.ListItemCollectionPosition in CSOM.

1

Another option is using REST, as detailed on IT Unity. This example processes everything on a library, but could be adapted for what ever you need. It recursively calls itself based on if the response has a next link.

function Get-Items{
    Param(
        [Parameter(Mandatory=$true,Position=0)][ValidateNotNull()]
        [string]$Url
    )
    $list = Invoke-SPORestMethod -Url "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')" 
    $itemType = $list.ListItemEntityTypeFullName 
    $digest = (Invoke-SPORestMethod -Url "https://tenant.sharepoint.com/teams/sitename/_api/contextinfo" -Method "POST").GetContextWebInformation.FormDigestValue 
    $listItems = Invoke-SPORestMethod -Url $Url
    Write-Host "Processing batch starting at "$listItems.results[0].Id -ForegroundColor Yellow

    foreach($li in $listItems.results){
        $newTitle = $li.FileLeafRef.ToString()
        if($li.Title -ne $newTitle){
            $id = $li.Id
            $pgurl = "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')/items($id)"
            Write-Host "Updating $pgurl" -ForegroundColor White
            $metadata = "{ '__metadata': { 'type': '$itemType' }, 'Title': '$newTitle'}"
            $item = Invoke-SPORestMethod -Url $pgurl -Method "POST" -XHTTPMethod "MERGE" -Metadata $metadata -RequestDigest $digest -ETag "*"
        }
    }
    Write-host "Batch complete" -ForegroundColor Green
    if($listItems.__next){
        Get-Items -Url $listItems.__next    
    }
}
cls
Get-Items -Url "https://tenant.sharepoint.com/teams/sitename/_api/Web/Lists/getbytitle('Library Name')/items?`$select=FileLeafRef,Title,Id&`$orderby=Id%20desc&`$filter=(ID%20le%20313031)"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.