This update includes support for the new Elastic Tables feature, XML data type and more.

Elastic Table Support

If you’ve started to use the new Elastic table type, you’re probably familiar with how the partitionid column is a crucial part of the identity of each record. This new version understands which tables are Elastic and therefore need to include this column when performing UPDATE and DELETE queries to ensure the correct records are affected.

The CreateMultiple, UpdateMultiple and DeleteMultiple messages are also crucial to getting the best performance out of Elastic tables. They’re also supported for many other table types too! This release uses these new messages for bulk data changes wherever possible.

All these changes are handled automatically behind the scenes so there isn’t anything different you need to do when constructing your queries. Just write your queries as you normally would and SQL 4 CDS will handle the rest for you.

XML Data Type

The XML data type in SQL Server provides a lot of flexibility for querying semi-structured data. XML is commonly used for storing things like form layouts and saved queries in Dataverse, so you can use a query like:

SELECT name,
       (CAST (formxml AS XML)).query('//cell[control/@datafieldname=''telephone1'']/labels/label[@languagecode=1033]')
FROM   systemform
WHERE  objecttypecode = 'account'

to find what label is used on different forms for the telephone1 attribute.

There is also a built-in method for base64-decoding data in the XPath query language, so you can get more useful values out of tables like webresource:

SELECT TOP 10 CAST (CAST ('' AS XML).value('xs:base64Binary(sql:column("content"))', 'varbinary(max)') AS VARCHAR (MAX))
FROM   webresource
WHERE  webresourcetype = 3

You can also use the FOR XML clause to generate XML data from other queries. A common pattern is to combine this with the newly-supported STUFF method to create comma-separated lists:

SELECT name,
       STUFF((SELECT ', ' + fullname
                      FROM   contact
                      where parentcustomerid = account.accountid
                      FOR    XML PATH ('')), 1, 2, '')
FROM   account

This is only the first release of XML support so there are probably various queries it does not currently support. Please let me know if you come across examples where it can be improved!

Bulk INSERT/UPDATE/DELETE queries

When you use a query which affects many records in one go, SQL 4 CDS will now use the CreateMultiple/UpdateMultiple/DeleteMultiple messages for greater efficiency where possible.

If an error occurs with one record, the query will normally stop immediately. This update includes a new query hint to allow the remaining records to continue to be processed to make it easier to handle large amounts of data where a small number of records may fail:

UPDATE account
SET    statecode = 1
WHERE  name LIKE '%(DO NOT USE)'
OPTION (USE HINT ('CONTINUE_ON_ERROR'))

Audit Queries

There are various fixes in this release to improve how SQL 4 CDS handles the audit table. This is built on the same technology as Elastic Tables, but with a few other quirks. This update encodes all those known quirks so you can continue to write queries as you want and SQL 4 CDS will understand how it’s possible to run them.

Bug Fixes

This release also includes several other bug fixes. Some of the notable ones are:

  • In Azure Data Studio, no rows were displayed in the results by default
  • Aggregate queries would return incorrect results when being run with multiple threads
  • Filters on the solution table combined with outer joins would trigger an error

Thanks to everyone who reported these issues on GitHub!

7 thoughts on “SQL 4 CDS v7.3 Released”

  1. I just stumbled over the “TelemetryClient” in the SQL4CDS Engine. Is there an easy way to turn this feature off without forking the project and have an “own” version without sending telemetry information?

    1. No, why does this cause a problem? It is not capturing any personal data, only the total number of query executions or error messages to help me improve it in future updates.

  2. First of all, thank you for your work and this great tool.

    I just have a little problem when I try to use the nuget (https://www.nuget.org/packages/MarkMpn.Sql4Cds.Engine/7.3.0/) package in a C# program.

    I tried your sample code from https://github.com/MarkMpn/Sql4Cds.
    The connection works but for the commands generate an timeout exception: “System.Data.SqlClient.SqlException: ‘Connection Timeout Expired. ”

    Any idea what could be the problem or am I using the wrong nuget package?

    I also tried to use this package https://www.nuget.org/packages/MarkMpn.SQL4CDS/7.3.0 but the dependecy XrmToolBox couldn’t be resolved.

    1. You’re using the correct package. It sounds like it’s trying to use the TDS endpoint but either it’s not enabled or it’s blocked by a firewall. Try setting the UseTDSEndpoint property to false on the Sql4CdsConnection

  3. How to avoid the message “DeleteMultiple has not yet been implemented” while executing a delete statement?

      1. Thanks Mark. Like you mentioned in the github, I ran the below query and received 0 results, but still adding “OPTION(USE HINT(‘BATCH_SIZE_1’))” to the delete statement worked like a charm 🙂 Thanks for your quick update on this.

        SELECT *
        FROM sdkmessagefilter
        INNER JOIN
        sdkmessage
        ON sdkmessagefilter.sdkmessageid = sdkmessage.sdkmessageid
        WHERE sdkmessagefilter.primaryobjecttypecode = ‘xxx_jobqueue’
        AND sdkmessage.name = ‘DeleteMultiple’;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.