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.


SharePoint Consulting

4 Comments so far

  1.   Stefan Heinz on October 24th, 2010          Reply

    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.   Generic viagra on March 8th, 2011          Reply

    This is really great blog post found here….. your blog having very unique content…. Thanks very much!

  3.   Torsten Mandelkow on April 22nd, 2011          Reply

    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.

  4.   <a href="http://www.trigent.com/" target="_blank">SharePoint Consultants</a> on March 26th, 2013          Reply

    Here’s an interesting read for Sharepoint 2013 Business Connectivity http://technet.microsoft.com/en-in/library/fp161238.aspx

Leave a Reply