Retrieve More than 5000 records

Cowards die many times before their deaths; The valiant never taste of death but once. – Shakespeare

We write codes to customize Dynamics CRM by fetching required data from CRM organization. But there are some record count limitation and we will discuss here.

We can retrieve data using FetchXML, Query Expression, OData and SOAP techniques. But due to record count retrieval limitation we are not able to fetch all records in one shot rather we get some limited records (5000) but there are still more records that cannot retrieved in simple SDK query techniques. Here we will discuss how to deal with this issue so that we can get all required data.

Why the record count limitation is exposed in Dynamics CRM?

1. To make the CRM performance better
2. Providing a flexibility for better controlled customization
3. Provides a configurable option ensuring more control on configuration

What is the Problem Developers/Customizers face due to this?

1. SDK retrieval methods returns limited records which requires more code to retrieve more records.
2. If the Dynamics Connector is use in projects then only the limited records are synched as per the record limit configured.
3. Any Customized Batch Jobs, Custom Web Pages or Custom Web Service which uses normal SDK data retrieval methods only returns limited record.
4. SSRS reports only show limited records as per the configured value

What is the Solution for this?

There are 3 solutions for this problem to deal with.

1. Remove the record count limitation completely.
2. Change the count limit to a higher value then 5000 as per your requirement
3. Customize your custom code to implement cookie concept to fetch more records

1. Remove the record count limitation completely.

This method will remove the limitation completely from the Dynamics CRM and it does not require any custom code manipulation. All CRM SDK record retrieval methods will return full required records count.

To remove this limitation you have to change the Registry key.

Steps:

1. Go to the server where the CRM application is installed.
2. Click Start -> Click Run -> Type regedità OK (This will open Window Registry Editor)
3. Select HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM
4. Check if there is a registry key already exist as TurnOffFetchThrottling.
5. If already exist then select the key and right click then Modify

6. In the modify window provide value data as 1.  i.e. other than 0. Then Press OK.

7. If the key is not there in the registry key panel then create new by right clicking on HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM then On the Editmenu, point to New, and then click DWORD Value.

8. Type TurnOffFetchThrottling, and then press ENTER.

9. Right-click TurnOffFetchThrottling, and then click Modify.

10. Type a number other than 0 in the Value data box, and then click OK.

11. Once done the grid will show as below screen shot.

12. Now Done. Click File menu and Exit Registry Editor.
13. DO an IISREST on Server.

Now the SDK data retrieval will method will fetch all required records count bypassing the limit.

Advantages

1. No extra code required in our SDK codes to retrieve more than 5000 records.
2. Dynamics Connector will sync all records bypassing the limit to target system.

Point of Considerations

1. The performance of the SDK query will be slow as obvious because it will try to retrieve all records in one shot.
2. Once you do this method, remove the count attribute from all your FetchXML if you are using in your code.
3. Sometimes the Time OUT issue may come if the record count will be huge and network is slow.
4. This can only be done in On-Premise Installation. In CRM Online you can’t do it.
5. Sometimes when you install a update rollup (UR) for Dynamics CRM then you may get the error like ‘Invalid Argument’ error and in CRM Trace :  Crm Exception: Message: A non-valid page number was received: 0, Error Code: -2147220989. To solve this issue follow the below link in Microsoft Support Knowledge Base which is answered by experts.

2. Change the count limit to a higher value then 5000 as per your requirement

To change the limit we have to open the SAL management Studio to connect the CRM Server. Actually the Max Record Count Value is stored in MSCRM_Config table in CRM DB.

Follow the below steps to do this.

Steps:

1. Open SQL Server Management Studio.
2. Connect to Dynamics CRM Server.
3. Click New Query to open the Query window.
4. Write the below Query.

USE MSCRM_CONFIG
GO
Update DeploymentProperties
Set IntColumn=-1
Where ColumnName='TotalRecordCountLimit'

NOTE:

-1 (or any negative number), will disable the limit but you can also change it to another limit. Note that you cannot lower the count below the number of rows displayed, which makes sense.

• Once updated then DO an IISREST which will apply the changes. This is mandatory.
• To increase the limit from 5000 to 10000 then change the query accordingly as below.

USE MSCRM_CONFIG
GO
Update DeploymentProperties
Set IntColumn=10000
Where ColumnName='TotalRecordCountLimit'

Points to Consider

1. The direct update in server setting tables through SQL query is not supported.
2. This only should be done once confirmed by Microsoft Support Team.
3. If you update the value more than 5000 in the column value then the query will retrieve the new limit record count but not all required records you are expecting if the record count available is more that value. So changing the value more than 5000 value does not solve the problem completely. You have to add custom code to fetch more record. But if you update the value as negative (-1) value then the limit will be bypassed.

3. Customize your custom code to implement cookie concept to fetch more

We can customize our code to fetch records page by page by using cookies. Let’s Discuss.

1. ODATA has a limitation where it can return only 50 records at a time. You can of course query more than that but it would be in batches of 50 records at a time.
2. Check here how to retrieve more than 50 records in OData.

relatedAccounts = [];
function onload() {
var serverUrl = Xrm.Page.context.getServerUrl();
var oDataUri = serverUrl + 
"/xrmservices/2011/OrganizationData.svc/AccountSet?$select=AccountId,
Name,&$filter=StateCode/Value eq 0";
GetRecords(oDataUri);
var totalRecords = relatedAccounts.length;
}
function GetRecords(url) {
jQuery.ajax({
type: "GET",
contentType: "application/json; charset=utf-8",
datatype: "json",
url: url,
async: false,
beforeSend: function (XMLHttpRequest) {
XMLHttpRequest.setRequestHeader("Accept", "application/json");
},
success: function (data, textStatus, XmlHttpRequest) {
if (data && data.d != null && data.d.results != null) {
AddRecordsToArray(data.d.results);
FetchRecordsCallBack(data.d);
}
},
error: function (XmlHttpRequest, textStatus, errorThrown) {
 alert("Error :  has occured during retrieval of the records ");
}
});
}
function AddRecordsToArray(records) {
for (var i = 0; i < records.length; i++) {
relatedAccounts.push(records[i]);
}
}
function FetchRecordsCallBack(records) {
if (records.__next != null) {
var url = records.__next;
GetRecords(url);
}
}

2. Fetch XML queries on the other hand will allow you to read up to 5000 records in one go with the ability to read the next 5000. Since requesting 5000 records at a time may not really be a good idea because of the network resources that would use and potentially slow down or even time out depending on the network speeds, it is always a good idea to read data in small manageable sets of records. If you want to use Paging concept then follow the below link which is published in MSDN Site.

    // Define the fetch attributes.
    // Set the number of records per page to retrieve.
    int fetchCount = 100;
    
    // Initialize the page number.
    int pageNumber = 1;
   
    // Initialize the number of records.
    int recordCount = 0;
    
    // Specify the current paging cookie. For retrieving the first page, 
    // pagingCookie should be null.
    string pagingCookie = null;

   // Create the FetchXml string for retrieving all child accounts 
   //to a parent account.
   // This fetch query is using 1 placeholder to specify the parent account id 
   // for filtering out required accounts. Filter query is optional.
   // Fetch query also includes optional order criteria that, in this case, 
   //is used 
   // to order the results in ascending order on the name data column.
  string fetchXml = 
    "<fetch version="1.0" output-format="xml-platform" mapping="logical" 
                                                            distinct="false">
      <entity name="account">
       <attribute name="name" />
       <attribute name="primarycontactid" />
       <attribute name="telephone1" />
       <attribute name="accountid" />
      <order attribute="name" descending="false" />
     </entity>
   </fetch>"; 
  Console.WriteLine("Retrieving data in pages\n"); 
  Console.WriteLine("#\tAccount Name\t\t\tEmail Address"); 
  while (true) \\ Loop started
  { 
     // Build fetchXml string with the placeholders. 
     string xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount); 
    
    // Excute the fetch query and get the xml result. 
     RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest 
     { Query = new FetchExpression(xml) }; 
     EntityCollection returnCollection = 
     ((RetrieveMultipleResponse)_service.Execute(fetchRequest1)).EntityCollection;
     foreach (var c in returnCollection.Entities) 
     { 
       System.Console.WriteLine("{0}.\t{1}\t\t{2}", ++recordCount, 
          c.Attributes["name"], c.Attributes["emailaddress1"] ); } 
        // Check for morerecords, if it returns 1. 
       if (returnCollection.MoreRecords) 
       { 
         Console.WriteLine("\n******\nPage number {0}\n*****", pageNumber); 
         Console.WriteLine("#\tAccount Name\t\t\tEmail Address"); 
          // Increment the page number to retrieve the next page. 
          pageNumber++; 
         // Set the paging cookie to the paging cookie returned from current 
         //results.
          pagingCookie = returnCollection.PagingCookie; 
        } 
        else 
        { 
          // If no more records in the result nodes, exit the loop. 
          break; 
        } 
      }

3. We can use Paging concept while retrieving data from CRM Server using Query Expression object. A sample query with paging concept given below.

// Query using the paging cookie.
// Define the paging attributes.

// The number of records per page to retrieve.
int queryCount = 5000; 

// Initialize the page number.
int pageNumber = 1;

// Initialize the number of records.
int recordCount = 0;

// Define the condition expression for retrieving records.
ConditionExpression pagecondition = new ConditionExpression();
pagecondition.AttributeName = "parentaccountid";
pagecondition.Operator = ConditionOperator.Equal;
pagecondition.Values.Add(_parentAccountId);

// Define the order expression to retrieve the records.
OrderExpression order = new OrderExpression();
order.AttributeName = "name";
order.OrderType = OrderType.Ascending;

// Create the query expression and add condition.
QueryExpression pagequery = new QueryExpression();
pagequery.EntityName = "account";
pagequery.Criteria.AddCondition(pagecondition);
pagequery.Orders.Add(order);
pagequery.ColumnSet.AddColumns("name", "emailaddress1");                  

// Assign the pageinfo properties to the query expression.
pagequery.PageInfo = new PagingInfo();
pagequery.PageInfo.Count = queryCount;
pagequery.PageInfo.PageNumber = pageNumber;

// The current paging cookie. When retrieving the first page
// pagingCookie should be null.
pagequery.PageInfo.PagingCookie = null;
Console.WriteLine("Retrieving account records in pages...\n");
Console.WriteLine("#\tAccount Name\t\tEmail Address");
while (true)
{   
   // Retrieve the page.
   EntityCollection results =_service.RetrieveMultiple(pagequery);
   if (results.Entities != null)
   {
     // Retrieve all records from the result set.
     foreach (Account acct in results.Entities)
     { 
       Console.WriteLine("{0}.\t{1}\t{2}", ++recordCount, 
       acct.Name, acct.EMailAddress1);
     } 
  } 
// Check for more records, if it returns true.
if (results.MoreRecords)
{
   Console.WriteLine("\n****************\nPage number {0}\n****************", 
  pagequery.PageInfo.PageNumber);
   Console.WriteLine("#\tAccount Name\t\tEmail Address");

   // Increment the page number to retrieve the next page.
   pagequery.PageInfo.PageNumber++;
   
   // Set the paging cookie to the paging cookie returned from current results.
   pagequery.PageInfo.PagingCookie = results.PagingCookie;
}
else
{
   // If no more records are in the result nodes, exit the loop.
   break;
 }
}

Check out the complete article in MSDN Site.

Happy CRMing 🙂