SQL 4 CDS 2.0.0 released!

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?

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.

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

Notifications

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.

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:

INSERT INTO listmember (listid, entityid)
SELECT '5610d244-aac2-4fa8-9b2f-56537c80bad0',
       contactid
FROM   contact
WHERE  firstname = 'Mark'

Managing polymorphic lookups

If you INSERT or 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'