It hasn’t been long since the previous release, but there’s some important fixes in this version along with some useful new features and performance improvements.

Fixes for non-FetchXML joins

There were a few bugs in the last few updates to do with the way SQL 4 CDS was handling joins that it couldn’t convert into FetchXML. This caused error messages like:

The given key was not present in the dictionary (#359)

or

Object reference not set to an instance of an object (#364)

when running the query. If you were joining tables in the metadata schema and added additional filters within the ON clause, it could also result in those filters being lost and you’d get back more data than expected (#360).

Many thanks to everyone who reported these errors on GitHub. The sample queries and execution plans really helped diagnose the problems.

NOT IN/NOT EXISTS Improvements

One of the example queries for the join errors made extensive use of NOT IN filters with subqueries. As well as fixing the original problem, I’ve also updated how the query optimizer works for these cases.

Consider this simple query:

SELECT name
FROM   account
WHERE  ownerid NOT IN (SELECT systemuserid
                       FROM   systemuser
                       WHERE  firstname = 'Mark')

In previous versions this would load in a list of all accounts, and a list of all users with a first name of “Mark”, then join the two together to find the accounts that didn’t match those users:

This works (except when it triggered one of the bugs mentioned above with the join), but isn’t very efficient.

With the optimizer change in this version, SQL 4 CDS converts this to a single FetchXML query. Dataverse does all the processing more efficiently within the back-end SQL database and only returns the matching rows:

Behind the scenes, SQL 4 CDS is now rewriting this as a LEFT OUTER JOIN and filtering out the records that match the related table:

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='account'>
    <attribute name='name' />
    <link-entity name='systemuser' to='ownerid' from='systemuserid' alias='Expr1' link-type='outer'>
      <filter>
        <condition attribute='firstname' operator='eq' value='Mark' />
      </filter>
    </link-entity>
    <filter>
      <condition attribute='systemuserid' entityname='Expr1' operator='null' />
    </filter>
  </entity>
</fetch>

Depending on your data you could see a huge improvement in query performance with this change.

UPDATE support for many-to-many intersect tables

Many-to-many relationships (such as contacts being members of multiple marketing lists) are implemented through an intersect table:

You can’t create, update or delete records in these intersect tables in the same way as normal tables. The records are created automatically by the Associate message, and deleted using the Disassociate message. (Marketing list members are special and are managed by the AddMemberList and RemoveMemberList messages instead).

SQL 4 CDS has long supported the INSERT and DELETE statements for these tables by using these alternative underlying messages, but trying to UPDATE has triggered the error:

Cannot update many-to-many intersect entities:
DELETE any unwanted records and then INSERT the correct values instead

Thanks to a little nudge from Daryl LaBar I’ve now removed this restriction. Although Dataverse doesn’t offer a message to update these records directly, SQL 4 CDS emulates it by simply removing the existing record and adding the new one. The two operations are combined in a transaction so if one fails then the other is automatically rolled back.

You can now use a query like this to selectively move members from one marketing list to another:

UPDATE listmember
SET    listid = (SELECT listid
                 FROM   list
                 WHERE  listname = 'New List')
FROM   listmember
       INNER JOIN
       contact
       ON listmember.entityid = contact.contactid
       INNER JOIN
       list
       ON listmember.listid = list.listid
WHERE  list.listname = 'Old List'
       AND contact.firstname = 'Move'

Sorting on Audit and Elastic Tables

I’ve come across a few differences with how sorting is applied to the Audit table and other elastic tables:

  • Multiple sorts don’t work. If you try to sort an elastic table by two different fields you’ll get an error The order by query does not have a corresponding composite index that it can be served from. You don’t get this error on the audit table, but the second sort seems to be silently ignored instead.
  • Picklist/choice/optionset fields. Normally, if you sort by one of these fields the results get returned in the order of the display name. For audit & elastic tables they’re sorted by the underlying number value instead.
  • Lookup & primary key fields. Standard tables sort these using the SQL Server rules for comparing guid values, but elastic tables compare them as strings instead.
  • Sorting on certain fields in the audit table has no effect, such as the attributemask and changedata fields.

This version improves SQL 4 CDS’s understanding of what sorts are safe to get Dataverse to execute as part of the FetchXML query, and which ones it should do internally.

14 thoughts on “SQL 4 CDS v7.6 Released”

    1. I’ve seen a few similar reports, but I haven’t been able to reproduce it myself so I’m struggling to fix it. Could you let me know the exact version of SSMS you’re using please?

      1. I get this from time to time as well. The same goes for INSERT and DELETE. Anything that modifies records it seems, because it never happens with SELECT statements.

        To fix it, I just restart SMSS and login again. Alternatively, if I’m not in a hurry then I can wait until the current session/token expires in a half hour or so, then do a couple simple queries to refresh it, which usually fixes it. So clearly an issue with the session/token rather than the plugin itself.

        I was previously using SMSS 18 and finally upgraded to 20.2 today, along with SQL 4 CDS v9.2.0 (no other extensions), but just had it happen again, so figured that I’d mention it still occurs.

        It is a bit frustrating, but for the amount of time this tool has saved me, I could put up with much, much worse and still be very satisfied. Thanks so much this Mark!

        1. This thread in the Microsoft Fabric Community seems related, and they figure it’s just an intermittent issue.

          https://community.fabric.microsoft.com/t5/Data-Warehouse/INSERT-is-not-a-supported-statement-type/m-p/3791690

          I’m not sure if it would be possible, but if SQL 4 CDS could allow forcing a session/token reset with the refresh token, since that could certainly be useful to avoid having to restart the app. Providing there’s nothing else beyond that, but once I get the error, it won’t go away without either restarting SMSS; or waiting for the current token to expire, as mentioned previously.

  1. Hi,
    Amazing project, any chance I’d like to request a concise getting started guide for the SQL4CDS project, covering prerequisites, project setup, live debugging for XrmToolBox & Azure Data Studio, step by step with 1 example, and contribution guidelines. This could help newcomers start contributing more effectively.

    1. You should be able to just clone the repo and build it with no special prerequisites. The post-build steps should copy the required assemblies to the XrmToolBox folder and run it, so if you just modify the debug settings of the MarkMpn.Sql4Cds project to refer to the location of your XrmToolBox installation and the name of a connection you want to debug it with you should just be able to hit F5 and start debugging.

  2. Hi Mark, love SQL4CDS, thank you for all your hard work on it.

    Not sure if this is the right place, but I had a feature request that I think would be very easy to implement. In the SQL4CDS results, GUIDs appear as hyperlinks which when clicked, open in my web browser as that specific record in Dynamics, which is fantastic. However I am often using Dynamics in an incognito tab, and so it doesn’t work with that. Would it be possible to add a right-click option on the GUID to copy the url to the clipboard? This would also make sharing results with colleagues convenient too.

    1. If you’re using incognito mode because of needing to log in to different systems with different identities, I’d suggest you look into the browser profiles feature instead. XrmToolBox allows you to select a different browser profile for each connection, and the links shown in SQL 4 CDS will use that profile so you shouldn’t need to use incognito tabs to achieve this.

  3. Hi Mark, why doesn’t the text search function move to the location of each result within the query window?

    It seems to undermine the search function if the user has to search themselves to find where it is highlighted?

    It would also be neat if it showed how many results there were

    All the best!

      1. thank you for an amazingly helpful ‘swiss army knife’.

        It has ‘saved the bacon’ on many occasions! đŸ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.