I’m pleased to announce that I’ve just released version 3 of SQL 4 CDS. The main updates in this release are:
- Metadata queries
- Support for virtual (___name) attributes
Since it’s initial release, SQL 4 CDS has queried standard entity data by converting your SQL to a FetchXML request. This release adds support for queries against metadata too, so you can write a query like:
SELECT entitylogicalname, logicalname FROM attribute WHERE description IS NULL AND attributeof IS NULL AND iscustomattribute = 1
This will show you the names of any of your custom attributes where you haven’t filled in the description – a useful quality check before you deploy your customizations.
Another useful example might be:
SELECT attr1.entitylogicalname, attr1.logicalname, attr2.logicalname, attr1.displayname FROM attribute AS attr1 INNER JOIN attribute AS attr2 ON attr1.entitylogicalname = attr2.entitylogicalname WHERE attr1.displayname = attr2.displayname AND attr1.logicalname <> attr2.logicalname AND attr1.iscustomattribute = 1 AND attr1.displayname <> ''
to show one of my pet hates – two attributes with the same name.
As well as
attribute, you can also get details on specific types of attributes. For example, to find all long text fields you could use:
SELECT entitylogicalname, logicalname, maxlength FROM stringattribute WHERE maxlength >= 4000 ORDER BY maxlength DESC
You can also access metadata of entities, one-to-many and many-to-many relationships, translations and optionsets.
For text that can be translated, such as the display name of an attribute, you can get your own localized version by accessing the
SELECT displayname FROM attribute
To get details of different translations you can join the
localizedlabel table to the corresponding
SELECT attr.entitylogicalname, attr.logicalname, l.languagecode, l.label FROM attribute AS attr LEFT OUTER JOIN localizedlabel AS l ON attr.displaynameid = l.labelid
Each query must have a single data source, which can be either:
- FetchXML Query
You can’t mix and match within a single query.
As this is the first release of the metadata querying feature I’m expecting there to be a few bugs – please let me know if you encounter any queries you’d expect to work but don’t!
You might have noticed that you can apparently retrieve
___name fields for optionset and lookup fields, which look like they should contain the display name of the selected value but always appear blank. This release makes these available to select, filter and sort by. You can now correctly run a query like:
SELECT accountid, name, industrycodename FROM account WHERE industrycodename IN ('Accounting', 'Brokers') ORDER BY industrycodename
and it will be converted to:
<fetch xmlns:generator="MarkMpn.SQL4CDS"> <entity name="account"> <attribute name="accountid" /> <attribute name="name" /> <attribute name="industrycode" /> <filter> <condition attribute="industrycode" operator="in"> <value>1</value> <value>4</value> </condition> </filter> <order attribute="industrycode" /> </entity> </fetch>
With this you can now see both the display name and underlying value for optionset and lookup fields without having to switch your options in the Settings dialog and re-run the query each time, as well as being easier to write the filter without having to look up the optionset values yourself.
Where possible the SQL will be converted to a native FetchXML query like this, but you can also run queries like:
SELECT accountid, name, industrycodename FROM account WHERE industrycodename LIKE 'Account%' ORDER BY industrycodename
that needs some extra processing within SQL 4 CDS itself.
Filters on the display name of lookup attributes are also supported, e.g.
SELECT name FROM account WHERE primarycontactidname = 'Mark Carrington'
is converted to
<fetch xmlns:generator="MarkMpn.SQL4CDS"> <entity name="account"> <attribute name="name" /> <link-entity name="contact" to="primarycontactid" from="contactid" alias="account_primarycontactid" link-type="outer" /> <filter> <condition attribute="fullname" entityname="account_primarycontactid" operator="eq" value="Mark Carrington" /> </filter> </entity> </fetch>
Again, please let me know if you find any queries where this doesn’t work as expected!
6 thoughts on “SQL 4 CDS 3.0.0 Released”
I’m trying the following select in SQL4CDS which I do not get working:
SELECT TOP 10
account AS al
ON al.accountid = speed1_associatedstaff.speed1_institutionid
WHERE al.owningbusinessunit LIKE ‘DF625F4D-DD62-E611-9A0F-005056AF09C4’;
I’m running the query against a CRM 365 Online instance. “speed1_associatedstaff” is a simple custom entity.
I’ve done quite some investigation into the FetchXML generated out of the query and to me it seems that Crm 365 Advanced Find does create a slightly different FetchXML compared to SQL4CDS. Error message I keep on getting is: “‘Speed1_associatedstaff’ entity doesn’t contain attribute with Name = ‘owningbusinessunit’.” error.
Is this a “know” limitation to SQL4CDS / FetchXML?
Thanks and best regards
Is that a limitation to
Hi Frank, I can’t reproduce this error in my tests. You seem to be missing a column list from the SELECT clause in your example, which should be giving you a syntax error when you run the query. If you do have a column list in there, do you have speed1_associatedstaff.owningbusinessunit listed?
Hi Mark, how can I improve performance when executing an UPDATE on a single table? I have less than 200 records to update a flag and taking over and hour before times out:
UPDATE [dbo].[account] WITH (NOLOCK) Set ag_allowprecall=0 WHERE ag_allowprecall=1 And accountnumber IN (
I’m new user to this great tool and not sure where can I can find basic info like this.
The only reason I can think of for that to take such a long time is if you’ve got plugins registered on the update of that field that take a long time to run. If you run the corresponding
SELECTstatement, do you get results back quickly? i.e.
SELECT accountid FROM [dbo].[account] WITH (NOLOCK) WHERE ag_allowprecall=1 And accountnumber IN (
If the SELECT statement runs quickly but the UPDATE statement runs so slowly, it must be related to plugins running on update. You should be able to avoid the timeout error by reducing the size of the update batches – under Settings, change the “Update and delete records in batches of x records” to 1 and try again – it won’t make the update run any faster, but it will do it one record at a time so you should see some feedback during the update and you should avoid the timeout.
Correct, SELECT runs in few seconds for 1k records.
I have changed settings so I’m showing now record progress when updating.
Account table must have some triggers since when updating as it takes 2-3 secs per record.
Thanks for your reply Mark.