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;
As well as declaring your own variables you can also use the built-in
@@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
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
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' )
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'
This query takes over 8 seconds, retrieved over 61,000
solutioncomponent and 385
solution records to produce 35 results
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.
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.
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!
The internal engine is now available for developers to use in your own applications much more easily – more on that in my next post!
15 thoughts on “SQL 4 CDS v6 Released”
Wow, you’ve been busy. I recently discovered this tool and as SQL Server guy this keeps me in familiar tooling. Downloading it now!
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.
There’s already a few issues on GitHub about this, e.g. https://github.com/MarkMpn/Sql4Cds/issues/191. Fix coming in a new version soon!
thanks Mark for quick reply.
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, I’ve reproduced this error now and I’ll try to include a fix for the next release
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)
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 184.108.40.206
Many thanks for any advice,
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.
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.
i have a query in fetchxml, then translate to sql4cds and it gives an error when executing: Sequence contains no elements
Can you share the query this is happening with please?
— Imported from FetchXML Builder
“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”
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)
(“invoice0”.psa_invoicenumber is null)
and this one the same: its easier
— Imported from FetchXML Builder
FROM invoice as inv
left outer join invoicedetail as invd on invd.invoiceid=inv.invoiceid
WHERE inv.psa_invoicenumber IS NULL