It’s time for another big update to SQL 4 CDS – this one’s been in the works for 3 months and has got some great improvements to its level of SQL support, performance, integration with SSMS and reusability in custom apps.

New SQL support: Variables

You can now declare and use variables within your SQL scripts to make them easily reusable. By putting changeable values in variables at the start of your script you can make it much easier to reliably change them and re-run the script with new values.

-- Define the variable names and types
DECLARE @accountname varchar(100), @firstname varchar(100), @lastname varchar(100);

-- Set a variable to a fixed value
SET @accountname = 'Data8';

-- Set a variable to a value from your data
SELECT @firstname = c.firstname,
       @lastname = c.lastname
FROM   account AS a
       INNER JOIN
       contact AS c
       ON c.parentcustomerid = a.accountid
WHERE  a.name = @accountname;

Global Variables

As well as declaring your own variables you can also use the built-in @@IDENTITY and @@ROWCOUNT global variables. @@IDENTITY is slightly different in SQL 4 CDS than SQL Server as it is an EntityReference type rather than an integer. The EntityReference type can be implicitly converted to a uniqueidentifer wherever a standard guid value is required.

By capturing the unique ID of a newly added record you can create scripts to add related records as well, e.g. add an account and related contact:

-- Define the variable names and types. Initial values can also be set at this point too
DECLARE @accountid AS UNIQUEIDENTIFIER,
        @contactid AS UNIQUEIDENTIFIER,
        @accountname AS VARCHAR (100) = 'Data8',
        @firstname AS VARCHAR (100) = 'Mark',
        @lastname AS VARCHAR (100) = 'Carrington';

-- Create a new account record
INSERT INTO account (name)
VALUES              (@accountname);

-- Save the newly created accountid to a local variable
SET @accountid = @@IDENTITY;

-- Create a new contact record linked to the new account
INSERT INTO contact (parentcustomerid, parentcustomeridtype, firstname, lastname)
VALUES              (@accountid, 'account', @firstname, @lastname);

-- Save the contactid too
SET @contactid = @@IDENTITY;

-- Set the primary contact on the account
UPDATE account
SET    primarycontactid = @contactid
WHERE  accountid = @accountid;

New SQL support: Flow Control

You can now also use IF, WHILE and GOTO statements to make more complex scripts. This can be particularly useful for scripts you want to reuse and where you currently need to check the state of the existing data before you run it. For example, if you want to make sure a record exists and only create it if it’s missing:

DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington';

IF NOT EXISTS (SELECT * FROM contact WHERE firstname = @firstname AND lastname = @lastname)
BEGIN
    INSERT INTO contact (firstname, lastname)
    VALUES              (@firstname, @lastname);
END

or you might want to make sure you’ve got at least 100 test contacts available in your dev system:

DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington', @requiredcount AS INT = 100, @currentcount AS INT;

SELECT @currentcount = count(*)
FROM   contact;

WHILE @currentcount < @requiredcount
BEGIN
    INSERT INTO contact (firstname, lastname)
    VALUES              (@firstname, @lastname);
    SET @currentcount += 1;
END

You can combine this with the @@ROWCOUNT global variable to perform an upsert operation:

DECLARE @name AS VARCHAR (100) = 'Data8', @websiteurl AS VARCHAR (100) = 'https://www.data-8.co.uk';

UPDATE account
SET    websiteurl = @websiteurl
WHERE  name = @name;

IF @@ROWCOUNT = 0
    INSERT INTO account (name, websiteurl)
    VALUES              (@name, @websiteurl);

New SQL support: PRINT

You can use the PRINT statement to get progress reports or other feedback from your SQL scripts. These are shown in the Messages pane in both XrmToolBox and SSMS:

DECLARE @firstname AS VARCHAR (100) = 'Mark', @lastname AS VARCHAR (100) = 'Carrington', @requiredcount AS INT = 100, @currentcount AS INT;

SELECT @currentcount = count(*)
FROM   contact;

IF @currentcount >= @requiredcount
BEGIN
    PRINT 'Already have ' + CONVERT(varchar, @currentcount) + ' contacts, no need to add more';
END
ELSE
BEGIN
    PRINT 'Only have ' + CONVERT(varchar, @currentcount) + ' contacts, inserting ' + CONVERT(varchar, @requiredcount - @currentcount) + ' more...';
    WHILE @currentcount < @requiredcount
    BEGIN
        INSERT INTO contact (firstname, lastname)
        VALUES              (@firstname, @lastname);
        SET @currentcount += 1;
    END
END

New SQL support: INSERT VALUES subqueries

When you’re adding records and want to include values from other records already in your system, you can now use subqueries:

INSERT INTO contact (parentcustomerid, parentcustomeridtype, firstname, lastname)
VALUES
(
    (SELECT TOP 1 accountid FROM account WHERE name = 'Data8'),
    'account',
    'Mark',
    'Carrington'
)

Query Optimization

A lot of work as gone on in the background to make queries run more efficiently, especially where a join can’t be converted into FetchXML. One good example is trying to find the solutions that contain a particular entity:

SELECT s.uniquename
FROM   metadata.entity
       INNER JOIN
       solutioncomponent AS sc
       ON entity.metadataid = sc.objectid
       INNER JOIN
       solution AS s
       ON sc.solutionid = s.solutionid
WHERE  entity.logicalname = 'account'

Before

This query takes over 8 seconds, retrieved over 61,000 solutioncomponent and 385 solution records to produce 35 results

After

The updated version runs in under 1 second and retrieved only the 35 records that are finally displayed

TDS Endpoint Compatibility

If you use Dynamics 365 online and have the TDS Endpoint enabled, you’ve had the option to use it to run your SELECT queries for some time. However, TDS Endpoint doesn’t support all tables and has some other limitations of the SQL syntax it supports. You might have hit errors if you had this option enabled when you run a query using something it doesn’t support.

In this update, SQL 4 CDS will first check your query to make sure it’s compatible with TDS Endpoint. If not, it will run the query using its own engine to avoid you hitting unexpected errors.

SSMS Integration

The SSMS version of SQL 4 CDS is greatly improved by this compatibility checking. In previous versions, any SELECT statement would be executed against the TDS Endpoint and produce errors if you tried to access an unsupported table. Now it will run this compatibility check and execute queries itself if TDS Endpoint doesn’t support them, so you can now run the same range of queries in both SSMS and XrmToolBox. The only exception is cross-instance queries, which still require XrmToolBox.

This update also brings the execution plan view to SSMS, so you can see how SQL 4 CDS will execute each query and get more details about FetchXML conversions.

XrmToolBox Updates

There are a few user interface updates in the XrmToolBox version too. Converting your SQL query to FetchXML is now a little easier with the new “Convert” button in the toolbar. Click this to see your query converted to FetchXML, or click the drop-down arrow next to it to convert it to other formats instead. The previous option to convert queries to M format for use in Power BI has also moved here.

If your query needs more than one FetchXML query to run it, you’ll see each one in a separate FetchXML tab so you can copy/paste or open them in FetchXML Builder individually.

You’ll also now be prompted as you close any SQL query windows to make sure you get a chance to save your queries before it’s too late!

Custom Applications

The internal engine is now available for developers to use in your own applications much more easily – more on that in my next post!

17 thoughts on “SQL 4 CDS v6 Released”

  1. Wow, you’ve been busy. I recently discovered this tool and as SQL Server guy this keeps me in familiar tooling. Downloading it now!

  2. Hello Mark,

    I have version 6.0.1 installed, when doing UPDATE a got the confirmation window to update records twice and if I have to run more updates this windows keeps showing multiple times.

    This was not an issue on previous versions.

    Thanks
    Luis

  3. Hi Mark.

    I have version 6.0.1 installed and when I try and do any bulk delete I get an error e.g.

    delete from contact
    where contactid = ‘00000000-0000-0000-0000-000000000000’

    Required field ‘FetchXml’ is missing
    System.ArgumentException: Required field ‘FetchXml’ is missing
    See the Execution Plan tab for details of where this error occurred

    The normal delete works fine (after clicking yes lots of times).

    Thanks,

    Malcolm

  4. Hi Mark,

    Reposting as also added to V5 page – sorry 🙂

    Thanks for creating such an amazing tool! We’ve used this extensively with great success.

    However we are currently having an issue. When we try to run delete queries like the one below records are retrieved correctly but the confirm dialog that gives the number of records to delete pops up multiple times (up to 10 times.)

    delete top (50000)
    from email
    where 1 = 1

    Records start to be deleted but the query fails quickly stating that the records do not exist. An example is below:

    Error deleting Email Messages – ActivityPointer With Id = 3d615bca-6ab7-ec11-983f-00224840e030 Does Not Exist
    Error deleting Email Messages – ActivityPointer With Id = 6a999c23-6ab7-ec11-983f-00224840e030 Does Not Exist
    See the Execution Plan tab for details of where this error occurred

    Similar behaviour deleting other record types.

    We’ve tried various setting in terms on the batch and worker thread size, using bulk delete etc. but nothing seems to help. Currently running version 6.0.1.0

    Many thanks for any advice,

    David

    1. I’ve already covered the multiple confirmation messages issue a few times, see e.g. https://markcarrington.dev/2022/04/11/sql-4-cds-v6-released/#comment-3741

      I can’t reproduce the “record does not exist” error. My best guess is that something else is affecting these records at the same time, e.g. another user also deleting records or a plugin trying to read the record as it’s being deleted. Could be worth investigating the plugin trace log and/or audit log if you have them turned on to see what’s happening.

  5. Hi Mark,

    Many thanks for the quick reply – really appreciated.

    I did see the other multiple confirmation posts but thought it might be affecting us differently as we are deleting records. I assumed that each confirmation might have led to the query being run multiple times – so later runs might then try to delete a record just deleted by another query. However I have tried to use KingswaySoft and that too is reporting that records don’t exist when we are trying to delete them.

    As per your advice I will make sure no one else is deleting records and also look to see if any plugins may be reading the record while we are trying to delete them.

    Best wishes,

    David

  6. i have a query in fetchxml, then translate to sql4cds and it gives an error when executing: Sequence contains no elements

  7. — Imported from FetchXML Builder
    select
    “invoice0”.Name as “name”
    , “invoice0”.TotalAmount as “totalamount”
    , “invoice0”.CustomerIdName as “customeridname”
    , “invoice0”.DateDelivered as “datedelivered”
    , “invoice0”.OwnerIdName as “owneridname”
    , “invoice0”.TotalLineItemAmount as “totallineitemamount”
    , “invoice0”.TotalTax as “totaltax”
    , “invoice0”.CreatedOn as “createdon”
    , “invoice0”.psa_InvoiceWriteOff as “psa_invoicewriteoff”
    , “invoice0”.psa_ProgramName as “psa_programname”
    , “invoice0”.InvoiceId as “invoiceid”
    , “invoice0”.CustomerIdType as “customeridtype”
    , “invoice0”.CustomerIdYomiName as “customeridyominame”
    , “invoice0”.OwnerIdType as “owneridtype”
    , “invoice0”.OwnerIdYomiName as “owneridyominame”
    , “account”.AccountNumber as “account.accountnumber”
    , “project”.psa_ProjectManagerName as “project.psa_projectmanagername”
    , “project”.psa_Number as “project.psa_number”
    , “project”.psa_ProjectManagerYomiName as “project.psa_projectmanageryominame”
    , “invd”.BaseAmount as “invd.baseamount”
    , “invd”.Description as “invd.description”
    , “invd”.PricePerUnit as “invd.priceperunit”
    , “invd”.Tax as “invd.tax”
    , “invd”.psa_ItemName as “invd.psa_itemname”
    , “invd”.psa_StartDate as “invd.psa_startdate”
    , “invd”.psa_HourTypeName as “invd.psa_hourtypename”
    , “invd”.psa_EmployeeName as “invd.psa_employeename”
    , “invd”.psa_HoursName as “invd.psa_hoursname”
    , “invd”.psa_EmployeeYomiName as “invd.psa_employeeyominame”
    , “item”.psa_ItemName as “item.psa_itemname”
    , “item”.psa_name as “item.psa_name”
    , “medewerker”.FullName as “medewerker.fullname”
    , “uursoort”.psa_name as “uursoort.psa_name”
    from
    Invoice as “invoice0”
    left outer join Account as “account” on (“invoice0”.CustomerId = “account”.accountid)
    left outer join psa_project as “project” on (“invoice0”.psa_Project = “project”.psa_projectid)
    join InvoiceDetail as “invd” on (“invoice0”.InvoiceId = “invd”.invoiceid)
    join psa_projectitem as “item” on (“invd”.psa_Item = “item”.psa_projectitemid)
    join SystemUser as “medewerker” on (“invd”.psa_Employee = “medewerker”.systemuserid)
    join psa_hourtype as “uursoort” on (“invd”.psa_HourType = “uursoort”.psa_hourtypeid)
    where
    (“invoice0”.psa_invoicenumber is null)
    order by
    “invd”.psa_startdate asc

  8. and this one the same: its easier
    — Imported from FetchXML Builder
    SELECT inv.name
    FROM invoice as inv
    left outer join invoicedetail as invd on invd.invoiceid=inv.invoiceid
    WHERE inv.psa_invoicenumber IS NULL

  9. I have started using this tool recently but i has my script running on SQL Management Studio but on this tool doesn’t work. It bring the following error

    “Subquery produced more than 1 row
    See the Execution Plan tab for details of where this error occurred”

    Below is the script:

    SELECT DISTINCT –top(10)*
    DI.statuscodename AS ‘Process Status’,
    DI.progres_id AS ‘Individual ID’,
    DI.progres_registrationgroupidname AS ‘Registration Group’,
    DI.progres_concatenatedname AS ‘Full Name’,
    DI.progres_primaryphonenumber AS ‘Primary Phone’,
    DI.createdon AS ‘Created On’,
    DI.modifiedon AS ‘Modified On’,

    (SELECT DISTINCT ‘; ‘ + SP.progres_spncategory2name
    FROM progres_specificneed SP
    INNER JOIN progres_individual DI ON SP.progres_individualname = DI.progres_id
    WHERE SP.progres_individualname = DI.progres_id
    AND SP.statuscodename = ‘Active’
    AND SP.progres_businessunitname = ‘Colombia – CO’
    AND SP.progres_specificneedstatusname in (‘Valid’, ‘Pending Review’)
    AND SP.progres_spncategory2name in (‘SGBV’,’Woman at Risk’, ‘Specific legal and physical protection needs’)
    AND (SP.progres_spnsubcategory2name in
    (‘Formerly associated with armed forces or groups’,
    ‘Security threat to UNHCR/partner staff or others’,
    ‘Detained/held in country of origin’,
    ‘Detained/held in country of asylum’
    )
    OR SP.progres_spnsubcategory2name is null)
    FOR XML PATH(”)) [Specific Needs]

    FROM progres_individual DI
    INNER JOIN progres_registrationgroup AS RG ON DI.progres_registrationgroupid = RG.progres_registrationgroupid
    INNER JOIN progres_specificneed SP1 ON DI.progres_id = SP1.progres_individualname
    LEFT JOIN progres_specificneedcode SPC ON SP1.progres_spncategory2 = SPC.progres_specificneedcodeid
    LEFT JOIN progres_specificneeddetailscode SPD ON SP1.progres_spnsubcategory2 = SPD.progres_specificneeddetailscodeid

    1. The FOR XML PATH syntax isn’t supported at the moment – I’d like to investigate adding support for the XML data type in a future update, but at the moment you might need to use the TDS Endpoint to get the results you need here.

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.