BCS and SharePoint Workspaces limited to 2000 items from external list

My client and I fought these issues for about a month all said and done so I figured I would post some of the things we learned along the way to save you a month of your life. J


The BCS is limited to 2000 items


True story. Out of the box business connectivity services throttles your connection to 2000 items. Yikes. Makes sense to keep performance in check but for most of my customers this has been an immediate issue. Luckily Microsoft anticipated this and gave us some Windows PowerShell to increase the limit or even turn it off. Luckily the BCS team wrote a post on how to manipulate this and saved me the effort. http://blogs.msdn.com/b/bcs/archive/2010/02/16/bcs-powershell-introduction-and-throttle-management.aspx Thanks guys.


Workspaces only likes 2000 items also


So we got through that and then we started syncing the list with SharePoint Workspaces and were quickly greeted with:


 


Too many items were returned. The limit 2000 was enforced.


What the heck? We got rid of the 2000 item limit. We did some more playing around and also found out that Outlook also could not sync the list. UGH. After opening a support ticket with Microsoft and working with Patrick G. we found the answer. There is a special registry edit you can make to allow the client BCS framework to overcome the 2000 item limit. The key is:


[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Business Data\Synchronization]


Then you need to create a dword for “Query Instances Limit”. Then set the value you would like. This registry change is on the client machine trying to do the sync.


Pretty cool stuff. Hopefully this saves you some time.


Shane


SharePoint Consulting

4 thoughts on “BCS and SharePoint Workspaces limited to 2000 items from external list”

  1. Shane – Thanks for that info. Didn’t have the issue (yet), but would have had it at some point.

    The link you refer to the BCS team does not work, it has two extra spaces at the end (%20). Just fyi.

  2. Hint for Outlook 2010 on 64bit: Create a QWord value instead of DWord for “Query Instances Limit”.
    And restart Outlook after changing the value.
    I used it for BCS External Lists in Outlook 2010 and it worked like a charm.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>