Or, the Power Platform Conference 2022 Edition! I’m finishing this post from the conference now – if you’d like to say hello while I’m here please get in touch!

Execute Messages

The big new feature in this release is being able to execute more Dataverse messages in your queries as well as just retrieving data from the normal tables. This opens up a lot of new possibilities, but one great example is working with audit data.

Querying the audit history table has been a common request in forums over the years, and there’s never been a great answer. The data is stored in the audit table, but not in an easy-to-use format. In recent Dataverse updates the important columns of this data aren’t directly accessible with a query any more either.

The supported way to access this data is with the RetrieveRecordChangeHistory function, but this isn’t easily accessible for non-developers. A number of tools have been created to make this easier, such as the Audit History Extractor tool in XrmToolBox. With this latest version of SQL 4 CDS you can now also get to this data with SQL:

SELECT contact.contactid,
       audit.createdon,
       JSON_VALUE(audit.oldvalues, '$.firstname'),
       JSON_VALUE(audit.newvalues, '$.firstname')
FROM   contact CROSS APPLY (SELECT *
                            FROM   RetrieveRecordChangeHistory(contact.contactid)) AS audit
WHERE  contact.firstname = 'Mark'
       AND JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

This will find all contacts with a first name of “Mark” and all the times the first name has changed in the past.

Table Valued Functions

Dataverse messages are exposed in SQL 4 CDS as both Table Valued Functions (TVF) and Stored Procedures. The example above uses the TVF syntax – the Dataverse message name (“RetrieveRecordChangeHistory”), followed by a list of parameter values in brackets.

The RetrieveRecordChangeHistory request takes two parameters – the “Target” (the ID of the record to get the audit history for), and paging information to get multiple pages of the audit history data. SQL 4 CDS automatically handles the paging for you, so only the first parameter is required. In this example I’m taking the contactid column from the contact table and using it as the parameter. The OUTER APPLY syntax ensures the RetrieveRecordChangeHistory function is called for each contact in the list.

If you wanted to get the history for a single contact based on its ID, you could do this in two ways. One would be to simply change the WHERE clause in the example above:

SELECT contact.contactid,
       audit.createdon,
       JSON_VALUE(audit.oldvalues, '$.firstname'),
       JSON_VALUE(audit.newvalues, '$.firstname')
FROM   contact CROSS APPLY (SELECT *
                            FROM   RetrieveRecordChangeHistory(contact.contactid)) AS audit
WHERE  contact.contactid = '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'
       AND JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

A simpler query would be to invoke the RetrieveRecordChangeHistory function directly without the CROSS APPLY:

SELECT createdon,
       JSON_VALUE(oldvalues, '$.firstname'),
       JSON_VALUE(newvalues, '$.firstname')
FROM   RetrieveRecordChangeHistory(CREATELOOKUP('contact', '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12'))
WHERE  JSON_PATH_EXISTS(audit.newvalues, '$.firstname') = 1

In this example I’ve had to use a custom function CREATELOOKUP to combine the entity name “contact” with the ID of the record to get the audit history for.

Stored Procedures

The same Dataverse messages can also be invoked using stored procedure syntax. This lets you capture the output values in different ways, but can’t be used in combination with the CROSS APPLY syntax to execute it automatically for multiple records.

DECLARE @contactid AS [MarkMpn.Sql4Cds.Engine.SqlEntityReference] = CREATELOOKUP('contact', '3cd7989a-7c51-4fb8-bb87-4f4f466a3e12');
EXECUTE RetrieveRecordChangeHistory @Target = @contactid;

Supported Messages

Not all Dataverse messages are supported. To be able to be used by SQL 4 CDS, a message must:

  1. have either no input parameters, or only simple scalar values (strings, numbers etc.) Messages that use complex input parameter types are not supported
  2. have either:
    1. no output values (message will be callable as a stored procedure only, not a table valued function), OR
    2. have only simple scalar type output values (strings, numbers etc.) When using the TVF syntax a single row will be produced with these output values. OR
    3. have only a single Entity or EntityCollection type output value of a known entity type.

Custom Actions and Custom APIs can be called provided they satisfy these requirements. It is not possible to define the entity type of an EntityCollection result from a custom action/API and so these messages will not be accessible.

The one exception to these rules are the RetrieveRecordChangeHistory and RetrieveAuditDetails functions, and any others that return audit details. The results returned for these messages will include all the columns from the audit table as well as the oldvalues and newvalues columns, which include a JSON format value that contains the values that were changed.

API Request Limits

Using a CROSS APPLY to execute a message for each row in a table could lead to a lot of API requests, and restrictions will be coming into force on how many you’re allowed to make. Take care not to overload your account using this pattern. There are safety limits built in to SQL 4 CDS that will automatically stop a query if it goes over a certain number of requests. By default this is turned on and set to 100 requests, and I’d recommend you keep this turned on.

Query Editor Support

You can find all the supported messages in the Object Explorer pane under the Programmability folder. Here you’ll find a list of all the messages that are supported by both the TVF and stored procedure syntax.

The messages will also show up as you type in the Intellisense dropdowns:

It also shows up as an Execute Message icon in the Query Plan viewer. This is the query plan for the first example query from earlier:

JSON functions

You might have noticed in my earlier examples I was using the JSON_VALUE function to get some data out of a JSON formatted column. This release includes this and the JSON_PATH_EXISTS functions to let you query JSON data.

As well as the audit data described earlier being presented in JSON format, these functions can also come in useful for other situations such as querying the systemform.formjson column to help find forms that include particular columns or controls.

String functions

This update includes support for the CHAR/ASCII and NCHAR/UNICODE functions to convert data between characters and the corresponding codes, which is very useful when working with special characters. It also adds the DATALENGTH function so you can calculate how much space your data is taking up.

Paging

I’ve blogged several times about some of the pitfalls of paging with FetchXML. This update detects one of these situations where there are child records in the query. In these situations SQL 4 CDS will apply its own paging logic rather than the standard FetchXML paging to ensure that records aren’t silently skipped as it moves between pages.

Query Optimisations & Fixes

Finally, there’s a bunch of optimisations in this release to help you get the results to your query faster.

One interesting one is if you have a join like:

SELECT account.accountid,
       contact.contactid
FROM   account
       INNER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
          AND account.name = contact.fullname
          AND account.name = 'Data8';

FetchXML only supports using one column for a join, and in this query we need to use two. However, because the value of account.name is fixed, so too must be contact.fullname. This query will now be translated to:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='contact'>
    <all-attributes />
    <link-entity name='account' to='parentcustomerid' from='accountid' alias='account' link-type='inner'>
      <all-attributes />
      <filter>
        <condition attribute='name' operator='eq' value='Data8' />
      </filter>
    </link-entity>
    <filter>
      <condition attribute='fullname' operator='eq' value='Data8' />
    </filter>
  </entity>
</fetch>

It also now converts groupings using the YEAR, MONTH and DAY functions to use the native FetchXML date grouping options for more efficient queries – previously only the DATEPART function was supported for this.

You may also have noticed incorrect NULL results when using the MIN aggregate function which has now been fixed.

24 thoughts on “SQL 4 CDS v7 Released”

  1. Keeps getting better and better and more powerful with each release! Wonderful work – great and indispensible tool.
    Thanks a heap !

  2. Hi Mark,
    I’m really impressed by the evolution of SQL4CDS. Adding RetrieveRecordChangeHistory to it is a great idea and helps so much. I’m grateful for this development.

    At the moment I’m stuggling how to use a date variable.

    If I try this:
    declare @dateFrom as Date;
    set @dateFrom = ‘2022-09-29’;
    select count(*) from contact c where c.modifiedon >= @dateFrom

    it leads to
    The date-time format for MarkMpn.Sql4Cds.Engine.SqlDate is invalid, or value is outside the supported range.
    See the Execution Plan tab for details of where this error occurred

    Can you please advice how to use it correctly?

    Best regards
    Olaf

  3. Hi Mark,

    Just want to thank you for all the work. This already was my favorite tool in the XRM Toolbox and now with this release being able to call Messages from queries it even became better.

    Thanks.

    Regards,

    Robert

  4. Hello Mark,

    I just wanted to say thank you!

    After 4 years of using I thought it’s time to leave a BIG thanks. This tool saved me a lot of time and headaches when it comes to selecting and even updating records within Dynamics.

    Keep up the good work! 🙂

    Cheers
    Carsten

  5. Hey Mark,

    thank you for your timesaving and helpful tool.

    I am using the version 7.0.3.0 and want to insert over 2000 sets of data into a table.

    Although setting the warning for inserting to “more than 4000”, it wants my confirmation for every insert.

    Do you have any idea, what I can do?

    Thanks in advance for your support.

    1. Do you have the option enabled to bypass plugin execution? If this option is enabled then every insert/update/delete will trigger a confirmation as it’s a potentially more dangerous operation.

  6. Hi Mark
    There seems to be something going on with the new releases. I have been using version 5.4.1.0 for a long time and below query for deduplication was working just fine, with half a million records of which 140K were duplicates and to be updated. When we went to do the same thing in higher environments, the person executing the query was running the latest version and it just hangs and times out. The old version completes processing it in a little over an hour. Since the query joins the same table to itself, it needs to retrieve about 8 million records before it can identify what to update. Even in some other scenarios we found that the latest version is a lot slower than the old version.

    We have been fortunately able to figure out how to roll back to the old version in the toolbox by replacing the DLL and thereby fix our issue, but thought to just let you know.

    Query looks something like this, and records are retrieved in increments of 5000, so it takes about an hour with the old version. But fails with the new version:

    UPDATE t1
    SET status = 1
    FROM entity1 t1
    INNER JOIN entity1 t2
    ON t1.id < t2.id
    AND t1.name = t2.name
    AND t1.code = t2.code
    AND t1.relatedrecordid = t2.relatedrecordid
    WHERE t1.status = 0

    Thanks

    1. There were some updates recently to push more of the filtering in this sort of query into the FetchXML – this gives the server more work to do but reduces the amount of data SQL 4 CDS has got to work with itself. Generally this makes the queries faster, but this may be an edge case where that doesn’t work so well.

      With the latest version you can try adding a query hint to prevent this and run the query very naively, just extracting a simple list of all records with FetchXML and then doing all the joining and filtering in memory:


      UPDATE t1
      SET status = 1
      FROM entity1 t1
      INNER JOIN entity1 t2
      ON t1.id < t2.id AND t1.name = t2.name AND t1.code = t2.code AND t1.relatedrecordid = t2.relatedrecordid WHERE t1.status = 0 OPTION(USE HINT('DEBUG_BYPASS_OPTIMIZATION'))

      You can also try using the TDS Endpoint if you are online and have that enabled, though you may hit the 2 minute timeout on that query.

  7. Hi Mark, thanks for the amazing tool as i have been using this for few months now. I am currently working with some date functions and got stuck with extracting the month name from the created on date. Do you think i can do this using the tool.

  8. Hi Mark, Amazing tool. Can I create stored procedure with this. I trying using the sql server syntax and it threw me an error. Please advice

  9. Hi Mark,

    Great tool and really useful, kudos for creating such a wonderful tool!

    I wanted to know if it is possible to create a script for table creation from this tool, it would be really helpful for data migration and staging environment setup.

    Thanks
    Shivendra

    1. No, CREATE TABLE and other DDL statements are not supported. It might be something I could look at in a future update, feel free to add an issue to GitHub so I can keep track of the request!

  10. Hi Mark, very useful tool,

    Worndering if I can flushout all table which contain any data in them ?
    Using this Query but it not giving me correct tables.

    SELECT E.displayname,
    E.logicalname,
    E.description,
    E.objecttypecode
    FROM metadata.entity AS E
    INNER JOIN
    metadata.entity AS t
    ON t.objecttypecode = E.objecttypecode
    Where @@ROWCOUNT > 0

    1. @@ROWCOUNT returns the number of records affected by the previous query, such as an INSERT or UPDATE. I don’t think it’s possible to do this with a single query, your best solution might be to use another XrmToolBox tool such as Fast Record Counter.

  11. Hi Mark,

    SQL 4 CDS is an amazing tool! I couldn’t do my job without it. Thank you so much for all your effort in creating and maintaining such a valuable tool.

    After reading this article I was trying to use the SQL in the first example (find all contacts with a first name of “Mark” and all the times the first name has changed in the past), but I’m getting the error “Undefined variable: @Expr1”. What am I doing wrong?

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.