How to Eliminate the 5000 Row Limit when Querying Dynamics 365 Dataverse Records
You may know that using FetchXML to query your data in the Dynamics 365 Dataverse will only return up to 5,000 rows in the results.
This has long been an inconvenient limit that even XRMToolBox utilities can’t get past.
But at last, there is a means to remove the limitation. Did you know you can now execute read-only SQL Queries from SQL Server Management Studio to access data in your cloud-based Dataverse environment? The SQL connection uses the Dataverse security model for data access. The user can query data for all Dataverse tables to which they have been given Read access.
SQL Server Management Studio will not impose a hard maximum size limit for results, however, there is a fixed five (5) minute timeout.
What You Need to Know to Use this Feature
If you want to take advantage of this feature, make sure these requirements are in place:
1. Make sure the “Enable TDS endpoint” setting is enabled in your environment.
This can be found in the Power Platform admin center under Settings > Features.
2. Make sure you have SQL Server Management Studio (SSMS) version 18.12.1 or later.
Connect to the Server using the desired organization's URL and your Entra user. Microsoft says "Only Microsoft Entra ID authentication is supported. SQL authentication and Windows authentication are not supported.”
3. Compose SQL queries using references to the database tables.
In its documentation on this feature, Microsoft advises “Any SQL operation that attempts to modify data will not work with this read-only SQL data connection.”
4. Optimize your queries to return only the limited data that you need to avoid the five (5) minute timeout.
5. Do not use NOLOCK when creating queries as this will prevent Dataverse from optimizing the query.
It's important to note that Microsoft currently lists this feature as “In Preview”, although it has been available for quite some time. Be sure to check the most updated Microsoft documentation to obtain further detailed information and to stay on top of any changes to the feature.
Want to Learn More About New Features in Your Microsoft Solutions?
Microsoft is constantly identifying ways to enhance its solutions and Stoneridge is in lock-step with them learning how we can bring those features to our clients. Get in touch with us today to learn more.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.