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.

Comments

• Anonymous
  May 10, 2010
  The comment has been removed

• Anonymous
  June 03, 2010
  I have try the first approach, but fail. I have created an external list with 2001 records after doing first approach with changing the default limit to 5000 and max to 20000, and the error still display. Should I  reconfigure the MOSS or do other changes?

• Anonymous
  December 13, 2010
  I have found another reason for getting the error.  When creating the external data source, if you map a column as an identifier, it cannot contain null values in the database. Just make sure whatever column you use as an identifier has a value in the database for each entry.

• Anonymous
  October 13, 2011
  This is an awesome post. Thank you!

• Anonymous
  September 04, 2012
  I am suffering with External lists for 3 days and they still don’t work; so if it is taking so much to configure a SharePoint 2010 external list, then SharePoint 2010 is the garbage of all garbage.

• Anonymous
  April 28, 2013
  I have still have the follwing message after I have followed the Sharepoint command ? I ' m still suffering pls help. Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator. Correlation ID:a72a0abe-7bc1-4a2f-ab77-910fdbb177e9

• Anonymous
  March 27, 2014
  Try the following LU; I followed the msdn post but it still didn't work as soon as i changed the timeout for the connection and the WCF throttle I was returning my data no problem. Add-PSSnapin Microsoft.SharePoint.Powershell -errorAction SilentlyContinue #Get-Command -Noun SPBusinessData $bdcProxy = Get-SPServiceApplicationProxy | where {$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')} $bdcProxy $dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bdcProxy Write-Host $dbRule   #Default and Maximum must be provided together. This increases the limit for external lists to 50000000. Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Maximum 100000000 -Default 50000000 $dbRule $throttleWCF = Get-SPBusinessDataCatalogThrottleConfig -Scope WCF -ThrottleType Size -ServiceApplicationProxy $bdcProxy Set-SPBusinessDataCatalogThrottleConfig -Identity $throttleWCF -maximum 1000000000 -default 500000000 $throttleWCF $throttleConn = Get-SPBusinessDataCatalogThrottleConfig -Scope Global -ThrottleType Connections -ServiceApplicationProxy $bdcProxy Set-SPBusinessDataCatalogThrottleConfig -Identity $throttleConn -maximum 200 -default 150 $throttleConn

• Anonymous
  April 10, 2015
  Wooh this is very informative article. Thank you so much..! <a href="staygreenacademy.com/.../"> SharePoint Development Tutorials</a>