SQL 4 CDS v9.1 Released

It’s been an exciting time for Dataverse recently – following on from the various newly-documented features in FetchXML we now also have a recycle bin feature! So definitely time for a new SQL 4 CDS update…

Recycle Bin Access

First off, check out the docs for this new (preview) feature and follow the steps there to enable it.

It can take a little while to take effect, but after you’ve enabled it you are able to delete a record and then go to the Power Platform Admin Center to recover it again! Some useful things to keep in mind with this feature:

• the deleted records are automatically cleared out from the recycle bin after a set period which you can define
• related records which are automatically deleted when you delete the parent are also automatically restored when you restore the parent
• the deleted records aren’t counted towards your storage usage
• you can delete individual records from the recycle bin if needed for compliance

In SQL 4 CDS (or any other tool!) you don’t have to do anything differently for a deleted record to end up in the recycle bin. If the feature is enabled then deleting a record will automatically move it to the recycle bin.

To access the data in the recycle bin you can now use a query referencing tables in the bin schema, e.g.:

SELECT name
FROM   bin.account

You can also see a summary of all deleted records using the deleteditemreference table:

SELECT deletedobjectname,
       deletedobjecttype
FROM   deleteditemreference
WHERE  validforrestore = 1

If you need to fully delete a record which is in the recycle bin you can delete either from the appropriate table in the bin schema, or the deleteditemreference folder:

DELETE FROM bin.account
WHERE       name = 'Contoso'

DELETE FROM deleteditemreference
WHERE deletedobjecttype = 'account'
      AND deletedobjectname = 'Contoso'

Elastic Table Lookups

I read a post recently from Linn Zaw Win about working with lookups to elastic tables. As he points out, the primary key of an elastic record is not just the guid as for other tables, but also the partition id. You can now access and update these values using the ___pid column, similar to how you might use the ___type column to set the type of record being referenced by a polymorphic lookup column.

UPDATE new_customtable
SET    new_elasticlookupid = '<guid>',
       new_elasticlookupidpid  = '<partitionid>'
WHERE  ...

Sharing & Unsharing Records

You can now share or unshare records by using INSERT, UPDATE and DELETE queries on the principalobjectaccess table. Internally these requests are converted automatically to the corresponding GrantAccess or RevokeAccess requests.

These requests work with three main columns:

• objectid – the ID of the record to share and unshare. This also needs to be paired with objectidtype to give the logical name of the table
• principalid – the ID of the user or team to share the record with. This needs to be paired with principalidtype to indicate if you are sharing with a systemuser or team
• accessrightsmask gives the access level to grant. This is a number which is made up by adding together the values of the permission you want to grant. For example, if you want to grant read & write access you would use a value of read (1) + write (2) = 3.

INSERT INTO principalobjectaccess
(objectid, objectidtype, principalid, principalidtype, accessrightsmask)
VALUES
('<account guid>', 'account', '<user guid>', 'systemuser', 3)

Note that when you run a DELETE query on this table, the record will not actually be removed but the accessrightsmask column will be set to zero, so no permissions are actually granted.

Debugging Support

If you are working with the SQL 4 CDS ADO.NET provider in your own projects, you may want to investigate why a particular query is not producing the results you expect, or why it’s running slowly. You can now inspect the execution plan of a query by using the new “SQL 4 CDS Visualizer” on a Sql4CdsCommand.

Bug Fixes

Some of the key bugs that have been fixed in this update are:

• DeleteMultiple has not yet been implemented. This error started occurring recently when trying to delete records in batches, and required changing the batch size to 1 to work around it. If you had to do this workaround you can now increase the batch size again.
• Red exclamation mark shown in results and records missing. This affected the display of results in the XrmToolBox version of the tool, and could occur if you joined tables not using the primary key.
• DISTINCT, UPDATE and DELETE queries limited to 50,000 records. These queries could trigger legacy paging which has a limit of 50,000 records. SQL 4 CDS now applies its own custom paging logic to avoid this limit.
• Missing records when ordering by an optionset value. Version 9 started using the newly-documented useraworderby FetchXML option to delegate sorting of results by an optionset value to the server. There appears to be a bug in the server logic which can cause records to be missed when using this, so this is now disabled again for the time being.