Export Limitation from Microsoft Dynamics CRM to Microsoft Excel

The 10,000 records export limitation from Microsoft Dynamics CRM to Microsoft Excel is certainly a hurdle for a lot of our users and administrators.  The question we often ask the customer is whether the ability to export more than 10,000 records  should be a global setting, which means it would apply for all the Dynamics CRM users within the organization  or should it be specific to the administrators of the systems.

Below are the two available options:

Option 1:

To default the export limit to a specific number and to make it available to the entire user community for the organization by default. In Microsoft Dynamics CRM 4.0 and Microsoft Dynamics CRM 2011, you can change the maximum number of records by changing a database value.  The steps are outline below:

• Open  SQL management Studio and connect to the appropriate sql instance
• Select the appropriate CRM database you want to make the change
• Click on New Query and run the following select statement on the OrganizationBase table

                      select OrganizationId,Name, MaxRecordsForExportToExcel from dbo.OrganizationBase

• You will then see the  current 10000 limit, which you can adjust to the desired number.
• Proceed with an IIS reset.(This will drop any user’s CRM connections).

Additionally, for those of you not comfortable with SQL, there are certain tools out there with a User Interface that will allow you to make the change without being connected to the SQL Management Studio.

The above example works great but might pose a possible performance issue when users across the organization are  exporting hundreds of thousands of records simultaneously.

The best practice is to narrow down your dataset through the advanced find as much as possible and then export to Excel once you have the desired filtered output.

Option 2:

Another alternate solution which seems a bit more controlled, would be to edit the query in Excel once the data has been exported out of Dynamics CRM.  Once you have done your advanced find and have the expected dataset:

• Click Export to Excel from the ribbon
• Once the Export Data to Excel webpage opens, select Dynamic Worksheet option. You can edit the  columns to select the appropriate fields if you have not done so.
• Click on Export.
• A pop-up will open asking you whether you want to open or save, select open.
• When the excel workbook opens, Enable Editing and Enable Content.

• Go to the Data Tab in the ribbon and click on Connection.

• Click on Properties.

• Click on the Definition Tab, and select Edit Query.

• You will get a pop-up window about “The query cannot be edited by the Query Wizard”, click ok.

• The Microsoft Query window will open. Select the SQL.

• A new pop-up Window showing the actual SQL statement appears. Remove “Top 10000” from the SQL statement and click OK to close the window.

• From the Microsoft Query window toolbar, click File and select Return Data to Microsoft Office Excel.

• Refresh your excel worksheet in the Data tab in the ribbon and you should see all the appropriate records now in the Excel worksheet.

For additional information or questions on exports from Microsoft Dynamics CRM, please contact one of our professionals at 855.437.7202 or at crm@mcgladrey.com. On average, our consulting team brings 15+ years of experience in CRM and can also help with other Microsoft Dynamics CRM support issues.

A la prochaine!

Jennyfer Mangaroo – Pennsylvania Microsoft Dynamics CRM partner

Jennyfer Mangaroo