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'
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!
Wow, you’ve been busy. I recently discovered this tool and as SQL Server guy this keeps me in familiar tooling. Downloading it now!
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
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.
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
Thanks, I’ve reproduced this error now and I’ll try to include a fix for the next release
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
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.
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
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
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
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
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
The
FOR XML PATHsyntax 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.