I’ve just released SQL 4 CDS 5.2.2! Upgrade today for a bunch of useful updates, fixes and improvements.
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.
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');
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.
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.