How to resolve 2000 items resolution in BCS

If you are working with a larger external list (more than 2000 items) in dev environment it works perfectly, but when you work in production environment, it throws some of the following errors and added in SharePoint Logs.

·         Timeout expired. BDC Runtime Object Model has throttled the response. The response from the service took more than '180000' milliseconds. The limit can be changed via the "Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

·         WCF Service Connector has throttled the response. The response from the WCF service contains more than '3000000' bytes. The maximum amount of data that can be read through WCF Service Connector is '3000000' bytes. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

·         Web Service Connector has throttled the response. The response from the web service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through Web Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.

·         Opening of a new connection is throttled. Maximum enabled connections ‘200’ for proxy are already been opened.

·         Database response throttled. Maximum number of rows that can be read through the database system utility is 2000.

This type of error normally occurs when BCS has throttled an external system call for taking too long, or trying to transfer too much data. By default this feature is enabled to prevent Denial of Service attacks that could adversely affect SharePoint or external system health by performing huge transactions. If you have a business need, you may want to increase the limits or disable them entirely, but keep in mind that you increase your farm’s exposure to Denial of Service threats.

To change that limit to a higher value, you need to be Farm Administrator and only can do the required changes using public APIs or PowerShell (SharePoint Designer and Central Administration UI won’t do it).

Normally when you start creating external lists you will notice that out of the box SharePoint limits external lists to 2,000 items, these are generally the first 2,000 items from the external content source. This limit is placed on External Content Types created in SharePoint Designer. The Business Connectivity Service Application has two limits, one for External Content Types created using SharePoint Designer and one for External Content Types created using code (Visual Studio). These limits can be changed using PowerShell as seen in the example below

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

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

 $BCSThrottle

 This will output the current limits. By default 2,000 with a maximum of 1,000,000 items if using Visual Studio. You can change the default values using the commands below. In this example we have increase the default limit to 20,000 items

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

 You now need to get the BCS Throttle Config data again

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

 Now run the command below to check your new limit.

$BCSThrottle

Scope        : Database

ThrottleType : Items

Enforced     : True

Default      : 20000

Max          : 1000000

This will increase the amount of items that can be retrieved in the browser, however there will still be a limit on the amount of items that can be taken offline using Outlook or SharePoint Workspace. This limit is still going to be 2,000 items. Unfortunately this has to be changed on each client by making a change to the registry. (thanks for Shane Young for blogging about this http://msmvps.com/blogs/shane/archive/2010/10/19/bcs-and-sharepoint-workspaces-limited-to-2000-items-from-external-list.aspx )

 Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\

Create a new “Key Business Data” with another sub-key “Synchronization”  (remove the quotes of course)
Now create a new Dword called “Query Instances Limit” set the decimal value to the number of items you want a user to be able to take offline. This is up to 30,000 with SharePoint Workspace.

Hope this was helpful! Stay tuned for more learning posts on BCS and External Lists.