Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

How to Increase the 50 Record Page Limit on OData Retrieve Responses for Dynamics CRM 2011

How to Increase the 50 Record Page Limit on OData Retrieve Responses for Dynamics CRM 2011

Rate This
  • Comments 1

An inherent limitation placed on retrieving data via CRM 2011’s OData endpoint (OrganizationData.svc) is that the response can only include up to a maximum of 50 records (http://msdn.microsoft.com/en-us/library/gg334767.aspx#BKMK_limitsOnNumberOfRecords_). For the most part, this does not impact your ability to retrieve the primary entity data sets because each response will provide the URI for a subsequent request that includes a paging boundary token.  As you can see below, the URI for the next page of results is provided in the JSON response object:

image 

Here is what the full URI looks like from the example response shown above, including the $skiptoken paging boundary parameter:

http://crmdev01:5555/ContosoCRM/xrmservices/2011/OrganizationData.svc/AccountSet?$skiptoken=1,'accountid','%7BFBF7BBA7-9368-E111-912C-0800275F9129%7D','%7B085B65E4-9268-E111-912C-0800275F9129%7D'

Similarly for Silverlight, a DataServiceContinuation<T> object is returned in the IAsyncResult which contains a NextLinkUri property with the $skiptoken paging boundary.  The CRM 2011 SDK includes examples for retrieving paged results both via JavaScript and Silverlight:

Nevertheless, this 50 record limit can become problematic when using $expand/.Expand() to return related entity records in your query results.  The expanded record sets do not contain any paging boundary tokens, thus you will always be limited to the first 50 related records. 

The next logical question – can this limit be increased?  Yes, it can.  50 records is a default limit available for update via an advanced configuration setting stored in MSCRM_Config.  Note that increasing the limit may only delay the inevitable for encountering results that exceed the limit.  It may also cause your responses to exceed the message limits on your WCF bindings depending on how you have that configured.  To avoid these scenarios, you may instead consider breaking up your expanded request into a series of requests where each result set can be paged.  Also, before proceeding, I’d be remiss not to point out the following advisory from CRM 2011 SDK:

**Warning**
You should use the advanced settings only when indicated to do so by the Microsoft Dynamics CRM customer support representatives.

That said, now on with how to modify the setting…

The specific setting that imposes the 50 record limit is called ‘MaxResultsPerCollection’ and it’s part of the ServerSettings configuration table.  Go here for the description of MaxResultsPerCollection and other ServerSettings available: http://msdn.microsoft.com/en-us/library/gg334675.aspx.

Advanced configuration settings can be modified either programmatically via the Deployment Service or via PowerShell cmdlet.  The CRM 2011 SDK provides an example of how to set an advanced configuration setting via the Deployment Service here: http://msdn.microsoft.com/en-us/library/gg328128.aspx

To update the setting via PowerShell, execute each of the following at the PowerShell command-line:

Add-PSSnapin Microsoft.Crm.PowerShell
$setting = New-Object "Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity"
$setting.LogicalName = "ServerSettings"
$setting.Attributes = New-Object "Microsoft.Xrm.Sdk.Deployment.AttributeCollection"
$attribute = New-Object "System.Collections.Generic.KeyValuePair[String, Object]" ("MaxResultsPerCollection", 75)
$setting.Attributes.Add($attribute)
Set-CrmAdvancedSetting -Entity $setting




Alternatively, you could use the PowerShell script provided in the CRM 2011 SDK to update advanced configuration settings: [SDK Install Location]\samplecode\ps\setadvancedsettings.ps1

To be able to execute scripts, you must first set your PowerShell execution policy to allow unsigned local scripts.

Set-ExecutionPolicy RemoteSigned

Then, execute the ‘setadvancedsettings.ps1’ PowerShell script from the SDK and pass in the configuration entity name (string), attribute name (string), and new value (Int32).

& "[SDK Install Location]\setadvancedsettings.ps1" "ServerSettings" "MaxResultsPerCollection" 75


After performing either of the two methods above to update your setting, you can verify that the change was successful by inspecting the ServerSettingsProperties table in MSCRM_Config database.

image

Since these configuration values are cached, perform an IISRESET on the application server if you want them to take immediate affect. 

Below are the results of a basic retrieve of AccountSet showing that more than the default 50 records were returned after applying this configuration change.

image

A few parting words…just because you can, doesn’t mean you should.

Austin Jones

Microsoft Premier Field Engineer

  • I received a follow-up question from an individual regarding the difference between the $top parameter and paging limits enforced by the MaxResultsPerCollection setting.  To clarify, the $top param deals with the total size of the query result, whereas the MaxResultsPerCollection setting is the upper limit to the paging mechanism.  I could see why this may be confusing because technically, the paging mechanism results in a SQL TOP expression.  Nevertheless, specifying $top in your OData query may result in limited query results, but it could also be further sub-divided into multiple pages if the total result is larger than the limit on page size.  

    For example, let’s say you have 120 Account records.  You issue an OData query that specifies $top=100.  The response will include the first page of 50 records and a Uri that can be issued in a subsequent request to retrieve the next page.  The response will also indicate that the query retrieved a total of 100 records.  If you were to increase the paging limit to 100 records, your OData query with $top=100 would return a single page with the entire result set.  In either case, you would only be retrieving the $top 100 Accounts, rather than all accounts.

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post