Fetch XML 5000 Records Limitation

Fetch all records

Have you ever tried to write a code which will get you all records from a specific entity? It's harder then you think it is! Everybody who is a bit aware of the CRM SDK thinks it should be a fetch statement like this:

<fetch mapping='logical'><entity name='account'><attribute name='accountid'/></entity>

WRONG!

This would only give you the first 5000 records in the database! It is written down in the SDK with small letters, but it could drive you crazy..

There are two solutions for this issue.

1) Add a registery setting to specify not to implement MaxRowsPerPage

2) Modify the fetch statement and merge several results

Here are the details for each solution

1st solution

Search in the SDK for the word "TurnOffFetchThrottling". You should add this as DWORD registery setting to HKLM\Software\Microsoft\MSCRM. Set the value to 1. You will now not have the 5000 records limit.

2nd solution

Modify your fetch statement to include paging and count numbers. Store all the data in an DataSet and perform that series of code over and over again as long as there is data coming.

Here's the script you should use to get all accountid's (for clarity and the ease of use I have added a function called "FetchDataSet").

private DataSet FetchAllAccountIds(){

int i=1;

bool bFinished = false;

DataSet dsAllData = new DataSet();

while (bFinished == false)

{

StringBuilder sbFetch = new StringBuilder();

sbFetch.AppendFormat("<fetch mapping='logical' page='{0}' count='5000'>", i);

sbFetch.Append("<entity name='account'>");

sbFetch.Append("<attribute name='accountid'/>");

sbFetch.Append("<attribute name='new_12_accountid'/>");

sbFetch.Append("</entity>");

sbFetch.Append("</fetch>");

DataSet dsTempResult = FetchDataSet(sbFetch.ToString());

dsAllData.Merge(dsTempResult);

if (dsTempResult.Tables[0].Rows[0]["morerecords"].ToString() == "0")

{

bFinished = true;

}

else

{

i++;

}

}

return dsAllData;

}

private DataSet FetchDataSet(string fetchXml)

{

string strResult = service.Fetch(fetchXml);

DataSet ds = new DataSet();

System.IO.StringReader reader = new System.IO.StringReader(strResult);

ds.ReadXml(reader);

return ds;

}

I hope this saves you some time!