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

Comments

# Ray Ozzie Leaving Microsoft; Microsoft Spending $500 Million Marketing Windows Phone 7; Poor IE9 Performance

Tuesday, October 19, 2010 8:07 AM by SharePoint Daily

Top News Stories Microsoft Searching for Bill Gates v3.0 as Ray Ozzie Leaves the Company (Softpedia)

# re: BCS and SharePoint Workspaces limited to 2000 items from external list

Sunday, October 24, 2010 3:45 AM by Stefan Heinz

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.

# SharePoint 2010: Recopilatorio de enlaces interesantes (XIII)!

Monday, November 01, 2010 1:10 PM by Blog del CIIN

Como a principios de cada mes, aquí os dejo una nueva entrega del recopilatorio de enlaces y recursos

# re: BCS and SharePoint Workspaces limited to 2000 items from external list

Friday, April 22, 2011 7:48 AM by Torsten Mandelkow

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.

# How to resolve 2000 items resolution in SharePoint Business Connectivity Services (BCS)

Wednesday, June 06, 2012 4:18 AM by Marcello Tonarelli's Microsoft Dynamics CRM Blog

If you are working with a larger external list (more than 2000 items) in dev environment it works perfectly

# How to resolve 2000 items resolution in SharePoint Business Connectivity Services (BCS)

Wednesday, June 06, 2012 4:26 AM by Marcello Tonarelli's Microsoft Dynamics CRM Blog

If you are working with a larger external list (more than 2000 items) in dev environment it works perfectly

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: