Friday, 25 October 2013

Issues with TurnOffFetchThrottling in MS Dynamics CRM 2011 - FetchXml Record Limit

I think i found a bug in MS Dynamics CRM 2011 today. We have a few UR 12 installed on our servers and for various reasons too long too explain, we had Turned off Fetch throttling.

This is done by adding a registry key to HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM called TurnOffFetchThrottling and setting the value to 1. This needs to be a DWORD type.

If you do this, then this FetchXml query will not work:
<fetch mapping="logical" count="2147483647" version="1.0"> 
 <entity name="account">
  <attribute name="name" />
It seems that by default MS Dynamics CRM 2011 will add one to the number of results that one is trying to get. So with Fetch Throttling on, i.e. the default, the above query will be parsed to the database like this:
select top 5001 "account0".Name as "name" , "account0".AccountId as "accountid" from AccountBase as "account0" order by "account0".AccountId asc
But if, FetchThrottling is off, when we pass Int.Max32 as the number of results required, like in the fetchxml query above, then when Dynamics CRM tries to add one to this value and parse it, it overflows and the query isn't run.

Admittedly it is extremely unlikely that this will be a problem, if you have 2147483647 instances of an entity in your database, I rather suspect that you need to be doing some archiving, but still it looks like a genuine issue.

It's interesting that the count value uses a signed integer rather than an unsigned one, which would give us twice as many records, i.e. 4294967295, or one record per every two human beings on Earth.

No comments:

Post a Comment