I’m very pleased to release version 2.0.0 of my SQL 4 CDS tool today!
If you haven’t come across it before, SQL 4 CDS is an XrmToolBox tool to query and manipulate your CDS data using regular SQL queries, making it accessible to a much wider range of users. You can install it today from the XrmToolBox Tool Library.
It takes quite a lot to jump from a version 1.0.x to a version 2, so what’s new?
Version 1 translated your SQL query into FetchXML and executed it, which made it easier to run the queries but didn’t give you the full power of SQL. There were still plenty of queries you couldn’t run because there was no FetchXML equivalent.
While still not supporting every bit of T-SQL (that would be near-enough impossible!), this release brings it a lot closer. While it still uses FetchXML as much as possible, it can now also transform those results to implement more SQL queries than before.
For example, you can now run queries that compare two field values, execute common functions such as
DATEADD, use a
HAVING clause, sort data with linked tables without having to worry about the order of your joins and much more.
Some example queries that you can run now that you can’t do with FetchXML alone:
Find accounts with more than 10 contacts
This query would not normally be possible as FetchXML does not have an equivalent for the
HAVING clause, but SQL 4 CDS 2 will handle it:
SELECT a.name FROM account AS a INNER JOIN contact AS c ON a.accountid = c.parentcustomerid GROUP BY a.name HAVING count(*) > 10
Find accounts that haven’t been modified since being created
FetchXML doesn’t have an option for comparing the values of two fields. SQL 4 CDS 2 handles this again, but watch out when running this on large data sets as it’s got to retrieve all the accounts in order to filter them itself.
SELECT name FROM account WHERE createdon = modifiedon
Find contacts created over a year after their account
A variation on the one above, you can now use more complex expressions such as selected functions, mathematical expressions etc. as part of your query too.
SELECT a.name, c.fullname FROM account AS a INNER JOIN contact AS c ON a.accountid = c.parentcustomerid WHERE c.createdon > DATEADD(year, 1, a.createdon)
Increment the score for all contacts called Mark
These same improvements can also be used as part of assignments in
UPDATE statements, e.g.:
UPDATE contact SET cdi_score = cdi_score + 1 WHERE firstname = 'Mark'
Sort by related tables
FetchXML imposes a restriction on sort orders. They have to be applied at the top-level entity first and then work down through joined tables. You can now apply sorts in whatever order you like:
SELECT a.name, c.fullname FROM account AS a INNER JOIN contact AS c ON a.accountid = c.parentcustomerid ORDER BY c.fullname, a.name
If you run a query that can’t be executed fully as FetchXML, you’ll see a warning like this:
Be sure to not simply copy & paste this FetchXML into your own code. Running this FetchXML directly will give you all the information you need to calculate the results, but you will need to do some post-processing on the results.
This release also includes Intellisense-style suggestions as you type. There are a few caveats to be aware of – as you type a
SELECT statement, the tool won’t offer suggestions until you start entering the
FROM clause, so if you want suggestions on the
SELECT clause itself then just enter
SELECT * FROM, enter the
FROM clause and then go back.
The metadata used to drive the suggestions is loaded in the background, so you may not get suggestions immediately. Give it a little time after entering the
FROM clause and you should start seeing suggestions appear.
I’ve tried to get the suggestions to appear when they feel natural to me. I’m interested in any feedback on this feature and how you’d like to see it work.
Managing many-to-many relationships
You can already use
DELETE to edit your data, but this release improves on that to cover the intersect entities used in many-to-many relationships. For example, you can use this query to add all your contacts called Mark to a specific marketing list:
INSERT INTO listmember (listid, entityid) SELECT '5610d244-aac2-4fa8-9b2f-56537c80bad0', contactid FROM contact WHERE firstname = 'Mark'
Managing polymorphic lookups
UPDATE a lookup field you can use the guid of the related record, but this only works for lookups that can only reference one entity type. For example, this would work:
UPDATE account SET primarycontactid = 'e3dd3640-9890-4371-b1cb-fb1b26f20ff3' WHERE name = 'Data8 Ltd'
However, this would not work as the
parentcustomerid field can reference both account and contact records, and there’s nothing to indicate which type is being referenced here:
UPDATE contact SET parentcustomerid = '65a7e7f7-0f09-4649-9182-56abc489a1e2' WHERE fullname = 'Mark Carrington'
This release introduces the
CREATELOOKUP function which can be used to solve this:
UPDATE contact SET parentcustomerid = CREATELOOKUP('account', '65a7e7f7-0f09-4649-9182-56abc489a1e2') WHERE fullname = 'Mark Carrington'