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.
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.
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.
API Request Limits
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:
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.
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
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.
18 thoughts on “SQL 4 CDS v7 Released”
Keeps getting better and better and more powerful with each release! Wonderful work – great and indispensible tool.
Thanks a heap !
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?
I’ve just created an issue for this date error on GitHub – https://github.com/MarkMpn/Sql4Cds/issues/257
For now this should work if you define your parameter as
I think this is a great enhancement made to the tool.
Thanks for this amazing tool!
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.
is it possible to start a scriptfile like this:
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! 🙂
Does SQL4CDs support updating metadata?
No, at the moment at least. If there’s a particular scenario you’d like to be able to use it for, could you please add some details to an issue on GitHub?
Thanks, I will do that.
thank you for your timesaving and helpful tool.
I am using the version 220.127.116.11 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.
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.
There seems to be something going on with the new releases. I have been using version 18.104.22.168 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:
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
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:
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.
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.
You can extract the month number using the DATEPART function, or you can convert the date to a string using the CONVERT function. Some conversion styles use the abbreviated month name so you could use that in combination with LEFT & RIGHT to extract just the month name part.
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
No, creating stored procedures is not supported. You can execute existing Dataverse actions using stored procedure syntax but not create new ones.