Friday, March 18, 2011

Metadata Columns and List View Lookup Thresholds

Steve Samnadda, Director, Technology

During a recent project we took advantage of SharePoint 2010 Managed metadata extensively to tag documents and items with over 12 different types of Metadata incuding: Product Type, Job Function, Region, Country, etc.

Early on, we ran into the following obtuse error while querying ListItems:

System.ArgumentException: Value does not fall within the expected range.   
 at Microsoft.SharePoint.SPFieldMap.GetColumnNumber(String strFieldName, Boolean bThrow)   
 at Microsoft.SharePoint.SPListItemCollection.GetColumnNumber(String groupName, Boolean bThrowException)   
 at Microsoft.SharePoint.SPListItemCollection.GetRawValue(String fieldname, Int32 iIndex, Boolean bThrow)   
 at Microsoft.SharePoint.SPListItem.GetValue(SPField fld, Int32 columnNumber, Boolean bRaw, Boolean bThrowException)   
 at Microsoft.SharePoint.SPListItem.GetValue(String strName, Boolean bThrowException)   
 at Microsoft.SharePoint.SPListItem.get_Item(String fieldName)   
 at ASP._controltemplates_discussionlibrary_discussionlibraryusercontrol_ascx.__DataBind__control4(Object sender, EventArgs e)   
 at System.Web.UI.Control.OnDataBinding(EventArgs e)   
 at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)   
 at System.Web.UI.Control.DataBindChildren()   
 at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)   
 at System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource)   
 at System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e)   

This was telling us the Metadata column we were querying didn't exist.  But wait!  The column really did exist and furthermore, when we were logged in as the Farm Administrator the query executed just fine.  So this error really didn't tell us anything.  Time to look at ULS logs!  Upon closer inspection, we found the following error messages:

Unable to open Lookup list '{5C66595B-E001-4E6B-899E-304C313FF567}'.[Error was 0x81020026]
Unable to open Lookup list '{2CB9B9B0-66E4-4FF5-AB14-1B3F97FD451F}'.[Error was 0x81020026]
Unable to open Lookup list '{5C66595B-E001-4E6B-899E-304C313FF567}'.[Error was 0x81020026]
Unable to open Lookup list '{2CB9B9B0-66E4-4FF5-AB14-1B3F97FD451F}'.[Error was 0x81020026]
Some lookup fields were omitted from the query results because the list exceeds the lookup column threshold. List URL: Documents

Could this error actually have anything to do with us?  After all, we are using metadata columns not lookups, right?  The answer is ... both ... and here's why:


1.) SharePoint implements metadata columns as Lookup Lists behind the scenes.  If you use Sharepoint Manager or some other tool you will notice there is a hidden list at the Site Collection Level "TaxonomyHiddenList".  Every hour the “Taxonomy Update Scheduler” Timer Job is responsible for syncing this list with Central Term Store Management.  This is really the list your columns are syncing with.


2.) SharePoint 2010 introduced the concept of Resource Throttling at the Web Application level.  There is a limit placed on the number of lookup lists that can be included in a query.  And this setting is different for Administrators and Regular Users. 

To find the setting go to Central Admin -> Application Management->Manage Web Applications.  Select General Settings->Resource Throttling.  The default values are as follows.  I increased our List View Lookup Threshold to 18 to accomodate our queries: