Business Connectivity Services: End User Implications – Part One: Threshold limit Errors

Business Connectivity Services: End User Implications – Part One: Threshold limit Errors
0
By admin BCS Blog Business Connectivity Services 5th June 2014

Introduction

Once you have created an external content type, content owners, business analysts and site owners will create Microsoft SharePoint® solutions to expose the data from external systems using External Lists, external data columns and Business Data Web Parts. However, using data from external systems, is not the same as using data from internal lists, such as document libraries, tasks, announcements and contacts. In this series of blog posts I will look at the errors users may see when a page is displaying data from an external system.

Note: If you are new to using external data in your SharePoint sites, you might like to read: “Introduction to external data”, which can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/introduction-to-external-data-HA102891586.aspx, and “Differences between native and external lists”, which can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/differences-between-native-and-external-lists-HA102771000.aspx.

This article is in six parts:

  1. Why this blog series
  2. Threshold Limit Errors
  3. Changing External System Throttling
  4. External Content Type Authorization Errors
  5. External System Authorization Errors
  6. Summary

In this first blog post, I will describe errors your may see as the result of external system throttling threshold limits.

First, as a reminder of what happens with internal lists, Office 365® and on-premises installation of SharePoint Foundation 2013 and SharePoint Server 2013 provide the following list throttling options.

  • List view threshold. A maximum of 5000 items are returned from a content database operation. When using a SharePoint on-premises installation this value can be amended using either the Central Administration web site or Windows PowerShell® at the Web Application level.
  • List view threshold for auditors and SharePoint server farm administrators. A maximum of 20,000 items can be returned from a content database operations.

When using external data these threshold DO NOT apply.

External System Throttling

Internal lists throttling and external system throttling is enabled by default. The external system throttling limits can be changed for on-premises installations of SharePoint by using Windows PowerShell® commands. With Office 365® the limits cannot be changed.

For example, by default, the number of external data items returned from a database query is limited to 2,000, with a database connection timeout of 60 seconds. A Windows Communication Foundation (WCF) connection is not limited by the number of items, but by the size of the data returned, which is 3 MB and a connection timeout of 60 seconds. Custom Web Parts can exceed the default throttling limits, but more on this and how to change the default throttling thresholds in the second part of this series.

However, no matter the value of the throttling limits, you may come across scenarios where the amount of data that is returned from the external system exceeds the limits. The method of overcoming the limits is to define filters on your external content types.

The rest of this blog investigates the error messages users see, when no filters have been created on the external content types or when the retrieval of data from the external system is taking too long.

The SharePoint components that are related to external content types and therefore where users will see the error messages, are:

Before looking at errors related to external content type related, let’s look at the threshold error messages you may see on internal lists and libraries.

List View Thresholds

When you create a view on an internal list or library that exceeds the list view threshold, the following message is displayed,

“This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator”

As shown in the following screenshot.

clip_image002

Now let’s explore the error messages you may see, related to external system threshold limits.

External content type Read List operation thresholds

The Read List operation (Finder method) threshold is defined by scope and throttle type. This threshold affects External Lists and Business Data Web Parts. The External List Item Picker is also affected by a threshold limit but this is not the same limit that is used for External Lists and Business Data Web Parts.

External Lists

When an external list is created from an external content type based, for example, on a database (Scope) and the number of items (Throttle Type) to be return exceeds the threshold limit, the following message is displayed:

“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’. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.”

As shown in the following screenshot:

clip_image004

More information on External Lists can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/create-an-external-list-HA102771010.aspx.

Business Data List Web Part

The Business Data List Web Part uses the same Scope and Threshold Type as the External List. The following error message is displayed within the Business Data Web Part when the items return from a database external system, exceeded the threshold limit.

“An error occurred while retrieving data from Adventure Works. Administrators, see the server log for more information.

Correlation ID:6259959c-f8b8-0034-3fb4-825fe72a8d0b

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’. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.”

As shown in the following screenshot:

clip_image006

More information on Business Data Web Parts can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/use-the-business-data-web-parts-HA102770986.aspx.

External Item Picker Threshold

The External Item Picker is a dialog box that is displayed when you are using:

  • External columns
  • Business Data Item Web Part
  • Business Data Actions
  • Word when using Quick Parts

Although the External Item Picker uses the same default Read List operations (Finder method) which can be used with External Lists and Business Data Web Parts, the External Item Picker uses a different limit to control the number of items displayed in the item picker. This limit cannot be changed and is set at 201.

For example, once you have created an external column on a list or library, then users can use the external item picker to select an item (row) from the external system and store that value in the external column. When the number of items returned by the external content type exceed this limit then in the External Item picker, the following error message is displayed:

“Warning: Too many results have been returned. This result set may be incomplete or truncated.”

As shown in the following screenshot:

clip_image007

Note: If the value from the external system is not displayed in the first 201 items returned, then the end user will not be able to select an external item to store as a value in the external column in their list item. The Search box in the External Item Picker, can only be used to return a subset of external data when a filter is defined.

You can also embedded external data in a Microsoft Word document using Quick Parts. You can then use the External Item Picker similar to how you used the External Item Picker in the browser to select the external data to include in your document. Similarly, a message is displayed if the results have been truncated,

“Warning: Too many results have been returned. This result set may be incomplete or truncated.

As shown in the following screenshot:

clip_image008

Summary

When you use Business Connectivity Services whether in Office 365® or on-premises installations of Microsoft SharePoint®, then throttling thresholds are enabled by default to prevent denial of service (DoS) attacks. You are most likely to see the effect of these thresholds when you create external content types with no limit filters or the retrieval of data from the external system is taking too long. A SharePoint server administrator can alter the thresholds, however, it is best to always configure filters on external content types. The next part of this article will describe the default threshold settings and how to change them.

Appendix: Filters

When creating your external content types (BDC models), use the following guidelines:

  • Define filters for the Read Item (SpecificFinder) and Read List (Finder) operations (methods) to limit the items that are returned from the external system.
  • When defining one or more filters, select a filter to be the default filter, especially if you will be using Quick Parts in Microsoft Word to embedded external data. In Word the External Item Picker does not display a drop down list to select a filter. When a filter is selected as the default filter, then that is the filter that will be used, although this is not obvious to the user as the name of the filter is not displayed.
  • Define default values for the filters. When developers use external content types in their solutions, then no items will be returned unless a default value for a filter is defined. However, when you first create an external list using the browser or SharePoint Designer, then no items will be returned from the external system, even when a default value is specified. You will need to train users to alter the default view and enter values for the filters for items from the external system to be displayed.
  • Define either a Limit filter or a Page filter on operations (methods) that return multiple items.
  • On input parameters, define system filters. On return parameters, define user filters.

References

 Back to Part Zero: End User Implications

 

admin / About Author
More posts by admin

Leave a comment