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!
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.
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.
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;
Not all Dataverse messages are supported. To be able to be used by SQL 4 CDS, a message must:
- have either no input parameters, or only simple scalar values (strings, numbers etc.) Messages that use complex input parameter types are not supported
- have either:
- no output values (message will be callable as a stored procedure only, not a table valued function), OR
- 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
- 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
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
newvalues columns, which include a JSON format value that contains the values that were changed.
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.
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:
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.
This update includes support for the
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.
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.
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
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.