Extending Dynamics CRM Default Result Limits for Querying and Exporting Records to Excel

Posted 11-28-2016 by David Steinbruch

Problem: CRM Not Returning all Records when Querying Programmatically

Recently, when programmatically querying CRM to get all active contacts from my organization, I couldn’t get more than 5000 contacts, even though there were more than 100.000 contacts on CRM. It turns out that CRM has this limitation to prevent extensive queries on the server. Bellow it is the code I wrote to get all the active contacts:

using (OrganizationService service = new OrganizationService("CRMConnString"))
{
	QueryExpression queryGetAllContacts = new QueryExpression();
	queryGetAllContacts.EntityName = "contact";
	queryGetAllContacts.ColumnSet = new ColumnSet("fullname", "cna_cnacontactid");
	queryGetAllContacts.Criteria.AddCondition(new ConditionExpression("statecode", ConditionOperator.Equal, "Active"));
	EntityCollection ec = service.RetrieveMultiple(queryGetAllContacts);
}

Solution

In order to get all the 100.000 contacts, there are some different solutions, for example changing Windows Registry or updating directly CRM Database by running a SQL script. But these solutions can have a big impact on the environment, so the best solution I’ve found was to use Paging cookie in the code. Below it is the modified code to retrieve all the active contacts:

QueryExpression queryGetAllContacts = new QueryExpression();
queryGetAllContacts.EntityName = "contact";
queryGetAllContacts.ColumnSet = new ColumnSet("fullname", "cna_cnacontactid");
queryGetAllContacts.Criteria.AddCondition(new ConditionExpression("statecode", ConditionOperator.Equal, "Active"));

int pageNumber = 1;
RetrieveMultipleRequest multiRequest;
RetrieveMultipleResponse multiResponse = new RetrieveMultipleResponse();

do
{
	queryGetAllContacts.PageInfo.Count = 5000;
	queryGetAllContacts.PageInfo.PagingCookie = (pageNumber == 1) ? null : multiResponse.EntityCollection.PagingCookie;
	queryGetAllContacts.PageInfo.PageNumber = pageNumber++;
	multiRequest = new RetrieveMultipleRequest();
	multiRequest.Query = queryGetAllContacts;
	multiResponse = (RetrieveMultipleResponse)service.Execute(multiRequest);
	exit.Entities.AddRange(multiResponse.EntityCollection.Entities);
}
while (multiResponse.EntityCollection.MoreRecords);

Problem: Default Record Limit When Exporting to Excel

Another problem I found was when exporting to Excel in CRM 2013, there is a 10,000-record limitation. This is a built-in limit on CRM to prevent extensive queries running on server. However, this can be very problematic for some cases the user wants to export more than 10,000 records and CRM only gives 10,000 records.

Solution

There are some solutions to tackle this issue. One updates directly Dynamics CRM database by running a SQL script, but the user needs to have access to the CRM SQL server instance. Another solution updates the Windows Registry, which can be very risky for the environment.

So, the best solution I’ve found was to run a console application as follows. This code can be run from a local machine and changes the record limit to 30,000.

It is also a good practice to after running the required export to Excel, to turn the record limit back to the default value 10,000.

class Program
{
	public static void Main(string[] args)
	{
            OrganizationServiceProxy _serviceProxy = null;
            IOrganizationService _service;

            Uri crmURI = new Uri("https://{hostname}/XRMServices/2011/Organization.svc");

            ClientCredentials clientCredentials = new ClientCredentials();
            clientCredentials.UserName.UserName = "username";
            clientCredentials.UserName.Password = "password";
            
            using (_serviceProxy = new OrganizationServiceProxy(crmURI, null, clientCredentials, null))
            {
                QueryExpression query = new QueryExpression();
                query.EntityName = "organization";
                query.ColumnSet = new ColumnSet()
                {
                    AllColumns = true
                };

                _service = (IOrganizationService)_serviceProxy;

                EntityCollection entities = _service.RetrieveMultiple(query);
                if (entities.Entities.Count == 1)
                {
                    if (entities.Entities[0].Attributes.Contains("maxrecordsforexporttoexcel"))
                    {
                        entities.Entities[0].Attributes["maxrecordsforexporttoexcel"] = 30000;
                        _service.Update(entities.Entities[0]);
                    }
                }
            }
        }
}

Add your comment

 
 
 

Comments

  • Issue on export to excel more than 5000 records! CRM 2016 ON PRIMES I AM EXPORT 5000 RECORDS BUT SOME ERROR OCCUR Error is: sql time out error please let me know