It’s only been a month since I released v6, but I’ve just published v6.1 with some useful new updates. You’ll need to make sure you’re running the latest version of XrmToolBox (1.2022.4.55) to run this new version.
The main improvement in the SQL engine in this release is a more in-depth handling of the various different data types provided by SQL Server.
Since v5, SQL 4 CDS has had good support for different data types and the conversions between them. This release builds on that by extending support for the different date/time related types and a more thorough handling of the precision, scale and length options for various types.
This means you can convert values to
time as well as the standard
datetime type and it should follow the same rules for those types as SQL Server.
You can also specify the precision and scale of the
numeric types and see the impact of those settings in any calculations.
Importantly for anyone using the
Sql4CdsConnection ADO.NET provider to integrate D365 data with external systems, the schema information it reports for a resultset accurately reflect all this information so it can be synchronised with the target system.
SQL 4 CDS has always supported the
NOLOCK table hint, and has more recently supported converting more hints to FetchXML. This release extends the use of query hints to control how SQL 4 CDS itself executes or translates queries.
OPTION (MAXDOP n)to override the “Use up to n worker threads” setting for an individual
OPTION (USE HINT ('BYPASS_CUSTOM_PLUGIN_EXECUTION'))to override the “Bypass custom plugins” setting for an individual
OPTION (USE HINT ('RETRIEVE_TOTAL_RECORD_COUNT'))to allow using the faster
RetrieveTotalRecordCountmethod of executing a
SELECT COUNT(*) FROM tablequery. Previous versions of SQL 4 CDS used to use this optimization by default, but this could lead to inaccurate results due to this using an out-of-date cached record count from the server. The option to use that by default has now been removed so you must explicitly add this option if required.
OPTION (NO_PERFORMANCE_SPOOL)to remove table or index spool nodes from the execution plan for correlated subqueries.
OPTION (USE HINT ('FETCHXML_PAGE_SIZE_n'))to force FetchXML queries to retrieve data in pages of
OPTION (USE HINT ('FETCHXML_PAGE_SIZE_100'))
If you include all the columns in your query, you might have seen the FetchXML query list every column individually. This update does a better job of converting this to use the
<all-attributes /> element instead to produce easier-to-read FetchXML queries.
When you run a query that includes a lot of columns, you might have seen a significant delay while you wait for each page of 5,000 records to come back from the server. There can be a lot of data to send for a query like
SELECT * FROM account INNER JOIN contact ... which leads to it using a lot of memory as well as taking a long time. This update automatically reduces the number of records it tries to load at once to keep the tool more responsive, including making it quicker to cancel the query and specify a smaller number of columns!
If you’re working across multiple tenants you might be using browser profiles to simplify logging in to each one with the right credentials. The latest version of XrmToolBox allows you to associate a browser profile with each connection:
SELECT a primary key or lookup field, the guid is shown in the result grid as a link – double-click on the link to open that record. In this update the link will now open with the browser profile you’ve selected for that connection. This will even handle cross-instance queries where each link might open in a different browser profile.
This release fixes one particular annoying bug causing a lot of popup confirmation messages when you run
It also works around a possible Dataverse bug if you try to do
GROUP BY queries on address fields, and a few edge cases with
DISTINCT queries, amongst others.