BCS and External List Learning – Part1

I am posting this article split into series covering specific BCS learning when working with customers.  In this post, I’ll cover a specific scenario where when browsing to an external list we might see the following error.

In most cases, this would be because of default throttling limit set in the Business Data Catalog system.  When the query is fired to the backend system, if the query returns more number of result than the default limit set for the Business Data Catalog system, we would see the above error message.  In my case, I implemented all CRUD operations on the “Customer” table of “AdventureWorks” sample database and the default query returns more than 2000 records.  2000 is the default item limit set and if the results we get from the backend system exceeds this limit, we would face the above error when trying to browse to the external list.  There are 2 solutions for this.

First approach

We can configure the default item throttling limit for the BCS application to allow more than 2000 records.  We can do that through SharePoint 2010 Management Console.  Steps follows:

Here we are creating a variable that will store the instance of the BDC service application proxy.  The –match parameter “Business Data Connectivity Service” is the name I get to see when I visit /_admin/ServiceApplications.aspx page at my environment.

After creating a proxy variable, I use the Get-SPBusinessDataCatalogThrottleConfig cmdlet and pass the proxy variable as one of the parameter.  This cmdlet will display the default configuration settings for databases as specified by the scope parameter.  I think, we can set similar configuration settings for web services and WCF services as well.  I’ll post it when I work on it.

Then I store the result of Get-SPBusinessDataCatalogThrottleConfig cmdlet in another variable as we will be using it to modify the default throttling configuration below.

The values for “Default” and “Max” are the ones we are interested in.  So, we’ll modify these to higher limit so that we can pull more number of records back from the data source.

We can then use the Set-SPBusinessDataCatalogThrottleConfig cmdlet to modify these 2 values as shown in this screenshot.

This will allow the external lists to display all records as long as the total records returned by the external source is less than 10000.

Second approach

The other approach is to set a limit parameter in the BCS model file.  SPD2010 is the favorite tool for creating BCS models, so I’ll show how to set it using it, but I assume this can also be done using VS2010.  I’ll assume that we have already created the BCS model and show how to edit in that case as by the time we encounter the above error message, we would have already had our model built.

Open SPD2010, click “External Content Types” left navigation bar, click open the External Content Types name, use the “Operations Design View” in the ribbon option, ensure that in the “External Content type Operations”, we select the “Read List” operation and choose “Edit Operation” from the ribbon.  Hit Next in the initial screen and in the next screen hit “Add Filter Parameter”.  Choose “(Click to Add)” link within the “Properties” section and choose “Limit” as the value for “Filter Type”.  Hit OK.  For the “Default Value” field within the Properties section type in 500 (or any number less than 2000, which is the default limit).  Hit Next and Finish.  Save the changes to this external content type.  Now if we browse to the external list, we should be able to see the data and would not hit the above error.

As I said, the above “generic” error “mostly” relates to BDC throttling, but may not “always” be the case.  To confirm that the error is because of throttling settings, we need to look at the ULS logs.  We should see the below error:

Error while executing web part: Microsoft.BusinessData.Runtime.ExceededLimitException: Database Connector has throttled the response. The response from database contains more than '10000' rows. The maximum number of rows that can be read through Database Connector is '10000'. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.
at Microsoft.SharePoint.BusinessData.SystemSpecific.Db.ThrottledIDataReader.Read()
at Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbEntityInstanceEnumerator.MoveNext()
at Microsoft.SharePoint.BusinessData.Runtime.EntityInstanceEnumeratorBase.MoveNext()
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()
at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItemCollection& listItems, String[]& fieldList)
at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()
at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()
at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform)

If this is not the exception recorded in the ULS log when we see the generic error in the UI.  Then we need to troubleshoot that further.

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