none
Database Connector has throttled the response - SharePoint online Powershell script

    Question

  • Hi All,

    i Have a external list created in SharePoint online o365. that list have some large data, since we couldn't extend throttled Microsoft has given an workaround that we can create filter or index.

    Creating filter or index is working find in the website. but my issue is i'm trying to run the below Power Shell script which throws the below error

    Exception calling "ExecuteQuery" with "0" argument(s): "Database Connector has
    throttled the response. The response from database contains more than '2000'
    rows. The maximum number of rows that can be read through Database Connector
    is '2000'."

    Powershell Script
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
    
    
    Function Get-SPOContext([string]$Url,[string]$UserName,[string]$Password)
    {
        $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
        $context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
        $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
        return $context
    }
    
    Function Get-ListItems([Microsoft.SharePoint.Client.ClientContext]$Context, [String]$ListTitle) {
        $list = $Context.Web.Lists.GetByTitle($listTitle)
       
    
        $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(100,"Company_x0020_Number")
    
    
    
    #$qry =[Microsoft.SharePoint.Client.CamlQuery]::""
        $items = $list.GetItems($qry)
        $Context.Load($items)
        $Context.ExecuteQuery()
        return $items
    }
    
    
    
    $UserName = ""
    $Password = "XXXXXX"   
    $Url = "https://ramesh.sharepoint.com/process/dms/"
    
    
    $context = Get-SPOContext -Url $Url -UserName $UserName -Password $Password
    $items = Get-ListItems -Context $context -ListTitle "IRC Shipment Info"
    
     Write-Host $items.Count
    $context.Dispose()



    Ramesh

    Wednesday, March 08, 2017 6:35 AM

All replies

  • Hi Ramesh,

    Please try to use row limit and CamlQuery.ListItemCollectionPosition Property in caml query.

    Check the PowerShell script below:

    $mQueryRowLimit = 100
    function getAllListItems($_ctx, $_listName, $_rowLimit = $mQueryRowLimit)
    {
        # Load the up list
        $lookupList = $_ctx.Web.Lists.GetByTitle($_listName)
        $_ctx.Load($lookupList)
    
        # Prepare the query
        $query = New-Object Microsoft.SharePoint.Client.CamlQuery
        $query.ViewXml = "<View>
            <RowLimit>$_rowLimit</RowLimit>
        </View>"
    
        # An array to hold all of the ListItems
        $items = @()
    
        # Get Items from the List until we reach the end
        do
        {
            $listItems = $lookupList.getItems($query)
            $_ctx.Load($listItems)
            $_ctx.ExecuteQuery()
            $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    
            foreach($item in $listItems)
            {
                Try
                {
                    # Add each item
                    $items += $item
                }
                Catch [System.Exception]
                {
                    # This shouldn't happen, but just in case
                    Write-Host $_.Exception.Message
                }
            }
        }
        While($query.ListItemCollectionPosition -ne $null)
    
        return $items
    }

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, March 09, 2017 9:04 AM
    Moderator