AggregateQueryRecordLimit exceeded exception in Microsoft CRM

We love to customize Microsoft Dynamics CRM solutions here at Beringer. We’ve been working with Microsoft Dynamics CRM since its inception, and we’re always finding innovative ways to implement the latest tools and help automate business processes. If you are looking for a CRM solution partner to help you with your CRM installation, make sure you pick the one that practices all of the Microsoft methodologies and best practices.

If you’ve played with querying Microsoft CRM data with FetchXML, then chances are that you’ve used some of their aggregate functions like sum, average, min, max, and count. You’ve also probably found out that if you try to aggregate large volumes of data, Microsoft CRM will throw this error message at you: AggregateQueryRecordLimit exceeded. Cannot perform this operation.

Limitations are normally set by an application to avoid tying up resources from processing and returning large volumes of data. I can appreciate those types of governors, but this limitation is frustrating because we simply want to retrieve a single aggregate value. My research shows that you can change this artificial limit currently set to 50,000 records in CRM Onpremise, but you can’t change it in CRM Online.

Is there a way to get around this limitation in CRM Online? I’m glad you asked 🙂

Step 1: Turn your FetchXML statement into its own function, and pass a start and end date

Step 2: Move the RetrieveMultiple call under a TRY statement

Step 3: Catch the AggregateQueryRecordLimit exceeded error code -2147164125

Since you know the dataset is too big for Microsoft to handle, you can strategically break up your start and end dates to reduce the dataset. In my example below, I’ve simply found a middle date between my start and end.  Now that you have your two new date ranges, you can now recursively call your function twice, once for each data set. The results will be summed together, returning the final results to the code that originally called the function. If one of your subsets of data is also too big, then the function will continue to break up the data and recursively call the function until every query is successful.

Can we help you with your CRM implementation? Please feel free to contact Beringer, a Microsoft Gold Certified Partner.