I’ve just released SQL 4 CDS 5.2.2! Upgrade today for a bunch of useful updates, fixes and improvements.

Audit table aggregates

Ever wanted to find which users are updating the most records, or which records are getting used the most? The audit table doesn’t naturally support many aggregate queries as it’s not stored in the main SQL database, but you can now run queries such as:

SELECT   su.fullname,
         su.domainname,
         su.userlicensetype,
         su.accessmode,
         max(a.createdon) AS lastlogindate
FROM     audit AS a
         INNER JOIN
         systemuser AS su
         ON su.systemuserid = a.objectid
WHERE    a.operationname = 'Access'
GROUP BY su.fullname, su.domainname, su.userlicensetype, su.accessmode
ORDER BY max(a.createdon) DESC;

This uses the audit table to find the last time a user accessed the environment. This could help you find users that aren’t regularly using it and could be disabled.

Metadata queries

I find myself using SQL 4 CDS to query metadata more and more. This update fixes one annoying bug so you can now use IN filters correctly like:

SELECT *
FROM   metadata.attribute
WHERE  entitylogicalname = 'account'
       AND attributetype IN ('String', 'Memo');

to get the details of all account text or multiline-text attributes.

It also fixes a bug when you try to use metadata in calculations:

SELECT entitylogicalname + '.' + logicalname
FROM   metadata.attribute
WHERE  entitylogicalname IN ('account', 'contact');

Aggregate query paging

It used to be that aggregate queries would only ever return one page of results, but there seems to have been some recent change where you can now navigate to later pages as well. You might have seen various posts from me before about the ways you can get unexpected behaviour with paging and this adds a new one.

This update automatically adds sort orders to aggregate queries to be able to take advantage of the better performance this change can give.

Multi-select Choices filtering

If you’ve got a multi-select Choices field, this update fixes the T-SQL CONTAINS function and the FetchXML containvalues function:

SELECT name
FROM   account
WHERE  CONTAINS (new_multiselectfield, '1 OR 2');


SELECT name
FROM   account
WHERE  new_multiselectfield = containvalues(1, 2);

Both these queries will give the same result. The first one uses the standard T-SQL syntax while the second one uses the custom FetchXML operator instead.

16 thoughts on “SQL 4 CDS 5.2.2 Released”

  1. Hello team.,
    I got error ‘An error occured when trying to dispaly this tool:unable to read user password’ after upgrade to 5.2.2 version.

    Reply

    1. This is an error that’s generated by the XrmToolBox connection library rather than SQL 4 CDS directly. How did you set up your connection in XrmToolBox, e.g. Connection Wizard, OAuth etc.?

      Reply

  2. Hi, 5.2.4.0

    I am trying to run an update query and I receive the response that 1 Transaction Lines updated

    However nothing actually gets updated. I am using V5.2.4.0 and when I try this on V4.0.0.0 it works and updates as expected

    Reply

  3. UPDATE upbeat_transactionline SET upbeat_RevenueGLAccount =’41005-25′ Where upbeat_RevenueGLAccount =’1005-25′;
    UPDATE upbeat_transactionline SET upbeat_RevenueGLAccount =’41005-30′ Where upbeat_RevenueGLAccount =’1005-30′;

    Reply

    1. I can’t see anything obviously wrong with the query. What type of field is upbeat_RevenueGLAccount?

      I wonder if you’ve got a plugin that’s overriding your changes somehow. What happens if you run the same sort of query to change the name of an account e.g. UPDATE account SET name = ‘account2’ WHERE name = ‘account1’? If that works but your original query doesn’t then there must be either a plugin that changes the field back during the update, or some other process that resets the value later.

      Reply

    1. Not currently, but it’s something I’m considering as part of a command line version for use in ALM pipelines. Is this something that would be useful in the XrmToolBox version too?

      Reply

  6. Hi Mark,

    After wave2 update to the power platform, the update function in SQL4CDS has been giving “An item with the same key has already been added.” error. it was a simple update on a lookup field on work order table.

    Currently in 5.2.5.0.

    Thanks!!

    Reply

      1. Hi Mark,

        After checking and uninstall-reinstall and testing other entities, I believe it is a connection issue. (I also got “Sequence contains more than one matching element” error too)

        It got confused when there were multiple connections, I was able to update it now! It is very strange this only happened since my organization updated to wave 2 the past weekend.

        Thanks again for your wonderful tool!

        Reply

  7. Hi Mark,
    I’ve just realized that current version does not support audit queries anymore. If I take your example “Audit aggregates” from above or any other simple way to query audit, I always get this error:
    RetriveMultiple call on Audit Entity must specify exactly one Condition set on objecttypecode at the top level filter
    See the Execution Plan tab for details of where this error occurred

    This occurs even with queries I’ve successfully executed with former versions of SQL4CDS

    Regards
    Olaf

    Reply

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.