SQL 4 CDS v9.5 Released - Mark Carrington

Updates to SSMS compatibility, bulk operations, optionset metadata access & more…

SSMS 21

The much-anticipated release of SQL Server Management Studio 21 is now here in preview! You can now install the latest SQL 4 CDS extension for it and access the tables that aren’t exposed via the TDS Endpoint and perform the INSERT, UPDATE and DELETE operations that it doesn’t support natively.

I’ve also removed the extensions for out-of-support versions of SSMS. If you’re not on at least SSMS 20, please upgrade!

.NET 8

The Azure Data Studio extension now requires .NET 8. If you are using the ADO.NET provider, this has also been updated to target .NET 8. This keeps SQL 4 CDS in line with the versions of .NET currently supported by Microsoft.

Bulk DML Improvements

Batch Size Adjustments

A common error when executing large INSERT/UPDATE/DELETE statements is a timeout with a message like:


Msg 10337, Level 16, State 1, Line 1
The request channel timed out while waiting for a reply after 00:01:59.9531245. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.

This typically happens due to the selected batch size being too large, either in the Settings dialog or using the BATCH_SIZE query hint.

It can be hard to find the optimal value for this setting, so SQL 4 CDS now treats this as a maximum rather than an absolute. It will dynamically adjust the batch size throughout the query to try and keep each batch execution to around 10 seconds, well within the standard timeout limits and fast enough to provide feedback that the query is running successfully.

Thread Count Adjustments

Another setting that is hard to predict the correct value for is the number of threads, or the “maximum degree of parallelism”. By default SQL 4 CDS takes a hint for this number from Dataverse itself. If this number is set too high you can run into the service protection limits, leaving your query paused with no explanation until it can retry and proceed.

In this update, SQL 4 CDS will again take this as a maximum instead of an absolute, and you will see the number of threads in use for large operations slowly ramp up towards this number. If it hits the service protection limits you will also see feedback that this has happened and how long it will be paused before trying again. The number of threads will automatically decrease when it hits the limits to try and keep the query executing smoothly.

Once the query has completed, statistics are available in the query plan to see how the number of threads and the batch size changed over the duration of the query.

We can see from this that, because the maximum degree of parallelism was set too high at 40 we hit the service protection limits 4 times. The mini chart for the records per minute show these as the gaps with no records being processed, then sudden bursts going up to 3,344 records per minute being inserted before hitting the limits again.

As the limits were hit the number of threads was also reduced automatically which we can see the actual degree of parallelism – this chart shows the number of threads gradually reducing.

`solutioncomponent` Support

You can now use INSERT/UPDATE/DELETE statements to work with solution components, adding & removing components or changing how much information is contained in your solution.

For example, you can add all the tables with a specific prefix into your solution using:

INSERT INTO solutioncomponent (solutionid, componenttype, objectid, rootcomponentbehavior)
SELECT '<guid>',
       1,
       metadataid,
       1
FROM   metadata.entity
WHERE  logicalname LIKE 'mcd\_%' ESCAPE '\'; -- _ is a wildcard, so use the \ escape character to match only the records that start "mcd_" and not "mcdx"

or you could copy all the components from one solution to another with:

INSERT INTO solutioncomponent (solutionid, componenttype, objectid, rootcomponentbehavior)
SELECT '<newsolutionid>',
       componenttype,
       objectid,
       rootcomponentbehavior
FROM   solutioncomponent
WHERE  solutionid = '<oldsolutionid>';

The columns in this table that we can work with in INSERT and UPDATE statements are:

Column	Description
`solutionid`	The unique identifier of the solution to add the component to.
`componenttype`	An optionset value indicating the type of component to add to the solution. The standard options are listed in the documentation. In this example I’ve used `1` which indicates an entity.
`objectid`	The unique identifier of the object to add to the solution. This can come from different places depending on the component type. For adding entities in this example I’ve used the `metadataid` column from the `metadata.entity` table.
`rootcomponentbehavior`	Indicates how much information from the object should be included in the solution: 0: all subcomponents are included 1: no subcomponents are included 2: only the basic metadata is included

The rootcomponentbehavior can be very important for making sure you don’t include things in your solution which don’t belong. These are equivalent to the following screens in the maker portal:

Editing the table segmentation properties in a solution

Ticking “Include all objects” is equivalent to rootcomponentbehavior = 0. Unticking “Include all objects” but ticking “Include table metadata” is 1, and both unticked is 2.

Metadata Improvements

SQL 4 CDS exposes the metadata schema to provide access to table, column and relationship information. In this version you can now also get the possible options for picklist fields in the metadata.optionsetvalue and metadata.globaloptionsetvalue tables.

You can use this to get all optionset fields and their associated possible values using a query like:

SELECT entity.logicalname,
       attribute.logicalname,
       optionsetvalue.label,
       optionsetvalue.value
FROM   metadata.entity
       INNER JOIN
       metadata.attribute
       ON entity.logicalname = attribute.entitylogicalname
       INNER JOIN
       metadata.optionsetvalue
       ON attribute.metadataid = optionsetvalue.attributeid;

or for global optionsets:

SELECT globaloptionset.name,
       globaloptionsetvalue.label,
       globaloptionsetvalue.value
FROM   metadata.globaloptionset
       INNER JOIN
       metadata.globaloptionsetvalue
       ON globaloptionset.metadataid = globaloptionsetvalue.optionsetid;

Bug Fixes

Some of the important bugs that are fixed in this update include:

Azure Data Studio extension was showing decimal values rounded to the nearest integer instead of the accurate decimal value
The error Must declare the scalar variable @Cond was being reported when executing queries with a specific type of join
Inefficient query plans were generated when joining various metadata-related tables such as dbo.attribute or dbo.entity due to inaccurate record counts being reported by RetrieveTotalRecordCount
Combining cross-table column comparisons with additional parental tables could lead to the error The multi-part identifier "table.column" could not be bound.

Supporting SQL 4 CDS

If you’re enjoying SQL 4 CDS, please remember you can support the tool by rating it on the XrmToolBox store. I’ve also just enabled GitHub sponsorship which can be a great way for your organisation to support the tools it uses.