After several recent updates to Dataverse I’m excited to release SQL 4 CDS 5.2 to take full advantage of the new platform features, along with several UI and query handling improvements.

Bulk INSERT/UPDATE/DELETE Performance

INSERT and UPDATE are some of the most common queries run in SQL 4 CDS. These can now take advantage of two new options introduced in the Dataverse platform.

The first is the option to spread the load of bulk operations across multiple servers. This is handled automatically for you whenever SQL 4 CDS detects you’ll be inserting, updating or deleting more than 100 records and using multiple threads.

To get the best out of this, please use queries that affect multiple rows in one query, e.g. run:

UPDATE account
SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb',
owneridtype = 'systemuser'
WHERE industrycodename = 'Accounting'

rather than the pattern I often see which is to run a query like:

SELECT accountid
FROM account
WHERE industrycodename = 'Accounting'

then manipulate the results in Excel to generate pages of individual UPDATE queries:

UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = 'abe98c67-91d8-4b17-af4d-f7dc6baddef1'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = 'c1647fac-b1c1-46ae-b2e1-a289a4375acb'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '3bbd3be4-cbf6-46e7-9383-f8bb91c6f217'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '60fe7ce9-a894-4847-99f9-1b76dc0d5d0e'
UPDATE account SET ownerid = '585f9666-ce32-46d6-b521-5f0bf65e94eb', owneridtype = 'systemuser' WHERE accountid = '54bfbc89-fffc-44cc-9505-24a7ec7c4b63'

as these queries will be run sequentially and won’t be able to take advantage of this optimisation.

Bypass Plugins

If you’re tidying up data in some way you may want to skip any plugins in your system.

You can now do this using the new “Bypass custom plugins” option in the Settings dialog:

Remember to turn it back on when you’re finished!

If you have this option turned on and don’t have permission to bypass plugins you’ll get an error.

Remembering your tabs

By default SQL 4 CDS will keep your tabs open when you re-open it so you can keep your queries across sessions. If you prefer you can turn this off in the Settings dialog:

Changing connection

If you have a query that you want to run on multiple instances, you used to have to create a new tab for each connection and copy & paste your query across. You can now use the new Change Connection button in the toolbar to switch the instance the current tab is connected to:

Accessing FetchXML

To get the FetchXML for your query you can double-click on the FetchXML Scan node in the execution plan:

This used to take you straight to FetchXML Builder, which can lead to an annoying delay if you just need the raw FetchXML. This now opens the FetchXML as a new tab in SQL 4 CDS itself:

From here you can click the FetchXML Builder button to edit it in FXB, or just copy & paste it to where you need it. The XML uses single quotes so you can paste it into your C# plugin code easily.

Reordering joins

You may now notice that the generated FetchXML uses a different arrangement of <entity> and <link-entity> elements than you’d expect from the order of your joins. This is because SQL 4 CDS will now re-order them automatically where possible to avoid some of the paging limitations I’ve blogged about earlier which could lead to your query missing some rows.

Minor UI changes

The “Connect” button on the toolbar is now in the Object Explorer pane:

From here you can add new connections to the Object Explorer, or remove existing connections.

You might notice the same icon in the main toolbar too. This is used to connect the current query tab to a Dataverse instance if it is not currently connected.

I’ve also added a number of help links to the Settings dialog which will take you to the relevant page in the Microsoft documentation that describes that option in more detail.

8 thoughts on “SQL 4 CDS 5.2 Released”

  1. Hi Mark,

    I have a small question: Is there an easy way to have NULL values exported as empty strings?

    Regards
    Olaf

  2. Good point. I’ve just realized I can use *case when … end* as well.
    I thought I’ve tried that in the past and it didn’t work. I’m happy it’s working now πŸ™‚

    Thanks
    Olaf

  3. Hi, I just upgraded to version 5.2.1.0 and now I get an error when attempting to connect to an environment. Error is:
    An error occured when trying to display this tool: Method not found:
    ‘System.Threading.Tasks.Taks’1
    McTools.XrmConnection.ConnectionDetail.get_MetadataCacheLoader()’.

    Any ideas on how to resolve this error?

  4. Hi Mark,

    I’m struggeling with containValues on MultiSelect Option Set

    When building a query with FetchXMLBuilder and transferring it SQL4CDS, it builds something like this (sorry I’ve no example using default fields, so i filled in placeholder here)
    WHERE = containvalues(778210018);
    Executing gives error message:
    Cannot convert System.Data.SqlTypes.SqlString to Microsoft.Xrm.Sdk.OptionSetValueCollection: containvalues(778210018)

    In fromer versions of SQL4CDS it worked using contains instead of containvalues:
    WHERE contains(, ‘778210001’)
    But this now gives an error message as well:
    The value passed for ConditionOperator.ContainValues is empty. Attribute Name: ap_contact_role, Attribute Id: 9c8dbdd3-3bff-46ac-8e2a-9dee618c9113
    System.ArgumentException: The value passed for ConditionOperator.ContainValues is empty. Attribute Name: ap_contact_role, Attribute Id: 9c8dbdd3-3bff-46ac-8e2a-9dee618c9113
    See the Execution Plan tab for details of where this error occurred

    The only working way I find at the moment is to do a string like on the field with postfix name:
    WHERE name like ‘%Insurer%’

    Regards
    Olaf

    1. Ups, my placeholder for fieldname does not show up, so here again using other type of brackets:

      WHERE (fieldname) = containvalues(778210018);

      WHERE contains((fieldname), β€˜778210001’)

      WHERE (fieldname)name like β€˜%Insurer%’

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.