I’m very pleased to announce the release of SQL 4 CDS version 1.0.6! Please update your existing versions in XrmToolBox to get some great new features…
COUNT(DISTINCT) Support
You can now use the count(DISTINCT col) aggregate function, e.g.:
SELECT count(DISTINCT firstname) AS distinct_names,
count(firstname) AS names,
count(*) AS contacts
FROM contact
WHERE statecode = 0
This will get the number of different first names, the number of active contacts with a first name and the total number of active contacts, and gets translated to the following FetchXML:
<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
<entity name="contact">
<attribute name="firstname" alias="distinct_names" aggregate="countcolumn" distinct="true" />
<attribute name="firstname" alias="names" aggregate="countcolumn" />
<attribute name="contactid" alias="contacts" aggregate="count" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch>
DATEPART support
You can also now group data using the DATEPART function, e.g. find out how many new leads you’ve received each month using:
SELECT datepart(year, createdon) AS year,
datepart(month, createdon) AS month,
count(*) AS leads
FROM lead
GROUP BY datepart(year, createdon), datepart(month, createdon)
ORDER BY 1, 2
This returns the year number, month number and the number of leads created in that month. The results are returned in date order. This translates to the following FetchXML:
<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
<entity name="lead">
<attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
<attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
<attribute name="leadid" alias="leads" aggregate="count" />
<order alias="year" />
<order alias="month" />
</entity>
</fetch>
Because the dategrouping attribute in FetchXML supports a few more options than the standard T-SQL DATEPART function, I’ve extended it in SQL 4 CDS to allow some additional values as the first parameter. You can also use fiscalperiod and fiscalyear to group your data by your fiscal periods instead of calendar periods, e.g.:
SELECT datepart(fiscalyear, createdon) AS year,
datepart(fiscalmonth, createdon) AS month,
count(*) AS leads
FROM lead
GROUP BY datepart(fiscalyear, createdon), datepart(fiscalmonth, createdon)
ORDER BY 1, 2
Sorting on link entities
You can now sort your query based on a field from a joined table, e.g.:
SELECT c.firstname,
a.name
FROM contact AS c
INNER JOIN
account AS a
ON c.parentcustomerid = a.accountid
ORDER BY c.firstname, a.name
Remember though that you can’t order by the joined table first and then the first table, so you can’t do:
SELECT c.firstname,
a.name
FROM contact AS c
INNER JOIN
account AS a
ON c.parentcustomerid = a.accountid
ORDER BY a.name, c.firstname
If you use an order that isn’t supported by FetchXML you’ll get the error Order already applied to later link-entity
UPDATE & DELETE Batch Sizes
You know you can write more than just SELECT statements in SQL 4 CDS? You can now control the number of records that are processed for UPDATE and DELETE in each batch, which can help avoid timeouts if each one takes a long time because of plugins etc. To control the batch size, click on the Settings button in the toolbar:
Bug Fixes
As well as these new features there are various bug fixes included:
- Fixed “Unhandled INSERT optimizer hints” error when running INSERT queries
- Fixed UPDATE query to set values to NULL
- Fixed IN & NOT IN criteria
- Fixed converting FetchXML to SQL (e.g. in FetchXML Builder) when the query contains a
<link-entity>with no content and aliases that aren’t valid SQL identifiers
Hi Mark,
First of all, thank you for providing such a valuable solution with SQL-4-CDS.
I was wondering if it is possible to perform an UPDATE query where I am setting a field value to some other field’s value. The example below shows what I am after and both fields would be of the same type. I have also tried using separate select and update statements with declared variables, as well as self joins. No luck so far.
i.e.
UPDATE new_someentity
SET new_fieldone = new_fieldtwo
WHERE new_name = ‘somevalue’
Thanks for the suggestion! It’s not currently supported, but it should be possible in a future update!
Thanks a lot Mark for this wonderful tool for sql 4 cds. Do you know when the support for declared variable will be released?
It’s something I’ve considered but don’t have any definite plans for yet. Do you have a particular use case for it?
yes
I am trying to check data profile in my db. The script is going though all tabels in the db to find the info I request.
I’d guess there’ll be more in that script that SQL 4 CDS doesn’t support beyond just variables. If you want to post the script as an issue on the GitHub site I can take a look, but you’ll probably be better off enabling the TDS endpoint on your instance and executing it through that
I am not sure where to enable the TDS enpoint from. Is that form sql 4 cds setting?
There’s some information on enabling and using it from SQL 4 CDS in the 2.1.0 release notes https://markcarrington.dev/2020/05/24/sql-4-cds-2-1-0-the-t-sql-edition/
Thanks , now I followed the instructions in the article, but I stil get the same messager “Unsupported statement: declare @ids table(idx int ….. “.
I would have send a screenshot of the whole thing but I do not know how on this reply. When I have enabled T-SQL endpoint, I got “T-SQL Endpoint (Unavailable – OAuth authentication required)”. So where can I give the authentication?
and by the way, T-SQL Endpoint item in the Object Explorer pane shows a green icon
It sounds like you’re using a username and password in your XrmToolBox connection. Add a new connection and use the OAuth connection option instead
I have now logged in with OAuth and still get the same message. I have now “T-SQL Endpoint” it is enabled and green. The Any other tips?
You might have to use SSMS to run your script. Now you’ve enabled the endpoint, take a look at https://markcarrington.dev/2020/05/13/cds-t-sql-endpoint-pt-1-connecting/ to get SSMS connected, then run your script from there
Is it possible to use aggregates in SQL 4 CDS e.g. SUM?
Yes, you can use anything that FetchXML supports – COUNT, MIN, MAX, SUM and AVG. COUNT also supports DISTINCT, e.g. COUNT(DISTINCT name)
Mark,
MAX works on some field but not others, when i try select max(createdon) from audit i get Aggregates are not supported
See the Execution Plan tab for details of where this error occurred, but when i select max(operation) from audit the query runs without error.
Is there something i can do to get the MAX function to work on createdon?
Thank you,
Jim
I do have a fix for this ready for the next release, I’m hoping to get this out maybe next week.
Is there a way to run scripts from a batch, like a command line or powershell?
Not yet, but it’s something I’m considering to let it be used as part of an ALM pipeline. Watch this space!
Would like to know if is possible to declare variables as in the next example to execute queries using this variable to update info as in the next example
DECLARE @FY22 int
select @FY22=new_fiscalyearid from new_fiscalyear where new_name = ‘FY22’
print @FY22
Thank you so much for this nice tool!!
Not yet, but this is one of the major updates I’m working on for the next version!
Thanks Mark!!! Really appreciate your job help us a lot.