locked
"The number of items in this list exceeds the list view threshold" Error in PowerShell Online script RRS feed

  • Question

  • Hi Folks,

    I am working to archive a SharePoint Online list which has around <25K items. We have to migrate the list content to three different SharePoint Online lists having records created in 2015, 2016 & 2017 respectively. I've migrated the entire content to a single SharePoint Online list and written a PowerShell Online Script to delete the records of 2016 and 2017 while keeping the records created in 2015.

    The PowerShell Online script failed and threw an error of 'The number of items in this list exceeds the list view threshold'. I've also set RowLimit to 1000 in Caml Query but it didn't help and throw an error of 'The var that's holding the caml object does not have a property rowlimit'.

    Please see my PowerShell Online script below.

    #Load SharePoint CSOM Assemblies
    Add-Type -Path (Resolve-Path "$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll")
    Add-Type -Path (Resolve-Path "$env:CommonProgramFiles\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll")
      
    #Variables for Processing
    $SiteUrl = "https://local.sharepoint.com/Test/ea/"
    $ListName="List - 2015"
    
    #Setup Credentials to connect
    $UserName = "kunal.basu@tstla.com"
    $SecurePassword = Read-Host -Prompt "Enter password" -AsSecureString
     
    #Set up the context
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
    $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
    $ctx.Credentials = $credentials
    $ctx.ExecuteQuery()
    
    #Get the List
    $List = $ctx.Web.Lists.GetByTitle($ListName)
    
    $camlQuery = new-object Microsoft.SharePoint.Client.CamlQuery
    
    $camlQuery.ViewXml = "<View>
    <RowLimit>1000</RowLimit>
    <Query>
     <Where> 
          <Geq>
              <FieldRef Name='Created' />
              <Value Type='DateTime'>2015-12-31</Value>
          </Geq>
     </Where>
    </Query>
    </View>"
    
    $ListItems = $List.GetItems($camlQuery)
    $ctx.Load($List)
    $ctx.Load($ListItems)
    $ctx.ExecuteQuery()
    
    write-host "Total Number of List Items found: "$ListItems.Count
    
        if ($ListItems.Count -gt 0)
    		{
    			#Loop through each item and delete
    			For ($i = $ListItems.Count-1; $i -ge 0; $i--)
    			{
    				$ListItems[$i].DeleteObject()
    			} 
    			$ctx.ExecuteQuery()
    			
    			Write-Host "All List Items deleted Successfully!"
    		}

    Can anyone help me for the RowLimit property supported in CSOM scripts for PowerShell Online.

    Thanks,


    Kunal


    • Edited by Kunal Basu Tuesday, May 16, 2017 1:38 PM
    Tuesday, May 16, 2017 1:16 PM

All replies

  • There is no way to limit the CAML query using RowLimit on a non-indexed field like the Creation date, because RowLimit is applied to the result set after it is retrieved and sorted.  What you'll need to do is build your query to limit the number of records retrieved based on an indexed field (ID is probably the easiest) and move your check for the Created date to the loop where you delete each record.  Take a look at the last paragraph in the following article.

    https://msdn.microsoft.com/en-us/library/ff798465.aspx


    Paul Stork SharePoint Server MVP
    Principal Architect: Blue Chip Consulting Group
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Tuesday, May 16, 2017 1:50 PM