Problem with limit of Items in External list

Question

My Web part consumes a list that presents this error:

The Database Connector limited the response. The database response contains more than '2000' rows. The maximum number of rows that can be read in the Database Connector is '2000'. You can change the limit by using the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet

Answer 1

Unfortunately, it's one of the main limitations of SharePoint External List!

In External List, If the number of retrieving items is more than 2000 items, the external list will not working properly. you will face a performance issue and it may be stuck to render!

Fortunately, you can exceed the default max limit (2.000 items) using Set-SPBusinessDataCatalogThrottleConfig cmdllet to the absolute max (25.000 items).

But unfortunately, exceeding the default max size is not recommended based on Microsoft support response. Moreover, the performance issue will not be solved!!

For more details check

• SharePoint 2013 BCS Service Application Throttle Management
• The external list limitations in SharePoint 2013 /2010

Answer 2

In my case there were two object as one for service and second one for proxy. And I get it resolved by below commands

$bcs = Get-SPServiceApplicationProxy | where{$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}

$BCSThrottle = Get-SPBusinessDataCatalogThrottleConfig -Scope database -ThrottleType items -ServiceApplicationProxy $bcs[0]

$BCSThrottle

Set-SPBusinessDataCatalogThrottleConfig -Identity $BCSThrottle -Maximum 1000000 -Default 20000