SQL 4 CDS 2.0.0 released!

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?

Beyond FetchXML

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 DATEDIFF and 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.

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

Notifications

If you run a query that can’t be executed fully as FetchXML, you’ll see a warning like this:

“Extra processing required” notification

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.

Intellisense

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 INSERT, UPDATE and 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:

Mark,
This is an amazing tool that has helped a database developer out more than once instead of having to fight the fetchxml. Love it.
I do have a question, how do I resolve this error on deleting records: “The request channel timed out while waiting for a reply after 00:01:59.9940521. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding.
Thanks for the work on this tool!