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

Metadata queries

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.

Translations

For text that can be translated, such as the display name of an attribute, you can get your own localized version by accessing the displayname attribute:

SELECT displayname
FROM   attribute

To get details of different translations you can join the localizedlabel table to the corresponding displaynameid attribute:

SELECT attr.entitylogicalname,
       attr.logicalname,
       l.languagecode,
       l.label
FROM   attribute AS attr
       LEFT OUTER JOIN
       localizedlabel AS l
       ON attr.displaynameid = l.labelid

Limitations

Each query must have a single data source, which can be either:

  • FetchXML Query
  • RetrieveMetadataChangesRequest
  • RetrieveAllOptionSetsRequest

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!

Virtual Attributes

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!

10 thoughts on “SQL 4 CDS 3.0.0 Released”

  1. Dear Mark.
    I’m trying the following select in SQL4CDS which I do not get working:
    SELECT TOP 10
    FROM speed1_associatedstaff
    INNER JOIN
    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
    Frank
    Is that a limitation to

    1. 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?

  2. 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 (
    ‘9611730’,
    ‘9625524’,
    ‘9625530’,….

    I’m new user to this great tool and not sure where can I can find basic info like this.

    Thanks

    1. 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 SELECT statement, do you get results back quickly? i.e.

      SELECT accountid FROM [dbo].[account] WITH (NOLOCK) WHERE ag_allowprecall=1 And accountnumber IN (
      ‘9611730’,
      ‘9625524’,
      ‘9625530’,….

      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.

      1. 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.

  3. hi Mark,
    great extension, I’m using it in Azure Data Studio.
    I want to query the localized labels with your query, but it complains about the unknown entity localizedlabel. .
    Do you have an idea why that entity is not present ?

    SELECT attr.entitylogicalname,
    attr.logicalname,
    l.languagecode,
    l.label
    FROM attribute AS attr
    LEFT OUTER JOIN
    localizedlabel AS l
    ON attr.displaynameid = l.labelid

        1. Ah OK – this example is from 4 years and 6 versions of SQL 4 CDS ago. The localizedlabel table doesn’t actually exist in Dataverse but was being exposed from the metadata by this earlier version – the way metadata-based queries is handled internally changed significantly in version 5, and as part of that update you can now access your own localized translations directly from the metadata.attribute table, but not retrieve the full list of translations. If this would be a useful improvement for you, please open an issue at https://github.com/MarkMpn/Sql4Cds/issues/new

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.