BCS External Data – unable to display the Web Part

One of the great features with SharePoint 2010, is the ability to pull in external data – using the Business Data Catalog – sorry, that’s the old name (2007) – it’s now Business Connectivity Services (BCS). 

For a recent project, I’ve been using a SQL Server database as a lookup – for metadata tagging of documents (ie. ClientID from a back-end system).   More on a “how-to” in further posts.

To do this, I used SharePoint Designer to create & configure the connection :

  • Connect to site
  • External Content Types –> New
  • Add connection + operations for Read, Read List, Update, Delete
  • I’m not going to cover the basics of BCS right now – perhaps another post

Anyway – you can simply click “Create Lists & Forms” – and SPD will create a new list definition – and allow you to browse the data much like a standard list (same UI at least).

BUT – when I’d first set this up – I was getting the following error :

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 : {GUID}

image

That’s is not very helpful !   What *IS* helpful, is the correlation ID (GUID) which will correspond with an entry in the SharePoint log – within the 14 hive (SharePointRootxxxxxxxxxx).

It turns out that there is an exception being logged when I view the list.   This exception (text) is split over many lines – the FULL text (including stack trace) is :

Error while executing web part: Microsoft.BusinessData.Runtime.ExceededLimitException: 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 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, Boolean fUTCToLocal, 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, Boolean fUTCToLocal, 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, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList) at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator() at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform) 153a18a6-fba3-4ca3-8e75-e9a9c652e2df

The only really important bit is the ‘message’ itself :

Microsoft.BusinessData.Runtime.ExceededLimitException: 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.

So – it looks like there is a limit of “2000” rows – this is not within the BCS configuration, but an underlying setting related to the BCS Service Application itself.

Only 2,000 rows ?    Seems a little “low”.   To resolve this, you can configure to be a higher number – eg. 10,000 – or you can simply turn this check to OFF.

NB.  This has been set for ‘performance reasons’ – so be aware of the potential risk.

Set throttling to OFF

Here’s the PowerShell script I ran :

(1) Get the proxy for the BCS (BDC) :

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

(2) Get the specific throttle rule – for that proxy :

$dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bdcProxy

(3) Set that rule to be off (enforced = false) :

Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Enforced:$false

Re-try BCS list

After running those statements, I just did an F5 on the list page – to refresh the SharePoint page – and I can now see the list of BCS entries.

Hope that helps you if you’re having troubles.

NB. Make sure you’re aware of the (potential) risk re: performance – if your BCS list (SQL Database Table) contains 2,000,000 rows for example.

Advertisements

5 thoughts on “BCS External Data – unable to display the Web Part

  1. Wow, this was really informative and helpful. Thank you very much! I was running into this same issue. It seems that you have to set a limit on the number of rows returned when you configure BCS connector or you’re out ot luck if the rows coming back are more than SP can handle.

    Like

  2. I have run the commands in question and yet still recieve the error: 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.

    Is there anywhere else to check?

    Like

  3. I like the valuable info you supply in your articles.

    I’ll bookmark your weblog and test once more right here frequently. I am quite certain I will be told many new stuff proper here! Best of luck for the following!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s