SQL 4 CDS 6.1 Released

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.

Data types

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 date, datetime2, datetimeoffset and 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 decimal and 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.

Query Hints

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 INSERT/UPDATE/DELETE query
• OPTION (USE HINT ('BYPASS_CUSTOM_PLUGIN_EXECUTION')) to override the “Bypass custom plugins” setting for an individual INSERT/UPDATE/DELETE query
• OPTION (USE HINT ('RETRIEVE_TOTAL_RECORD_COUNT')) to allow using the faster RetrieveTotalRecordCount method of executing a SELECT COUNT(*) FROM table query. 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 n records, e.g. OPTION (USE HINT ('FETCHXML_PAGE_SIZE_100'))

Lots of columns

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!

Browser Profiles

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:

When you 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.

Bug fixes

This release fixes one particular annoying bug causing a lot of popup confirmation messages when you run INSERT/UPDATE/DELETE queries.

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.