I’m pleased to announce SQL 4 CDS 4.0.1 for working with your Dataverse data in SQL. This includes a number of improvements to SQL language support, error reporting and SSMS support.

XrmToolBox Download the XrmToolBox tool

Microsoft SQL Server Management Studio Download the SSMS plugin

SSMS Improvements

When it was initially released, the TDS endpoint required you to connect on port 5558, and the SQL 4 CDS SSMS plugin only applied to connections on that port. As you can now connect to TDS endpoint on the default port, the SSMS plugin will now also activate for connections to orgname.crm.dynamics.com as well as orgname.crm.dynamics.com,5558

XrmToolBox Improvements

The SQL 4 CDS XrmToolBox tool now accepts the CTRL+E keyboard shortcut as well as F5 to match SSMS.

SQL Language Improvements

This release fixes an error when executing a query combining aggregates, joins and the ORDER BY <index> syntax, e.g.

SELECT   name,
         count(*)
FROM     account
         INNER JOIN
         systemuser
         ON account.ownerid = systemuser.systemuserid
GROUP BY name
ORDER BY 2 DESC;

It also fixes an error when inserting values into many-to-many relationship tables, e.g.:

INSERT  INTO listmember (listid, entityid)
VALUES                 ('<guid>', '<guid>');

12 thoughts on “SQL 4 CDS 4.0.1 Released”

  1. Hi Mark,
    wonderful tool, I like it really much.

    Today I came across something which seems like a possible bug to me.
    When I fetch a generated field with postfix name like in this statement:
    select accountnumber, statuscode, statuscodename from account

    Field statuscodename is fetched for the first 5000 records only, but it’s empty on the following records. Are these fields not supported on the following pages?

    Thanks
    Olaf

    Reply

  3. Hey Mark,

    Excellent tool! Thanks for stopping the deprecation of SQL developers within the D365 realm!

    Trying to do a bulk update on some records that requires a complex query. Any plans for the future to enhance to tool for more keywords such as LEN or LIKE?

    Best Regards.

    Reply

    1. Thanks Jeffrey! Yes I’m working on support for more complex queries at the moment, but LEN and LIKE should already be working. There’s no FetchXML equivalent for LEN so it’s not as efficient, but SQL 4 CDS should do the required processing to make it work. LIKE can be translated directly to FetchXML so there should be no problem there. Can you post the query you’re having problems with or add it as an issue on GitHub please?

      Reply

      1. Hi Mark,

        Thanks for the response. I actually did figure that LEN and LIKE are working. Are you working on anything for CHARINDEX anytime soon? I am current trying to bulk update some URLs and am receiving the following error:

        Unknown function: CHARINDEX(‘/ReportSection’,new_individualreport2)

        Kind Regards.

        Reply

  4. Hi Mark,

    thanks for the new version. I’ve just tried out some group by statements and noticed they do not work with virtual columns:
    select statecodename, count(*) from account group by statecodename
    leads to an error message.

    Would it be possible to add support for virtual columns here?

    Thanks
    Olaf

    Reply

  5. Hello Mark, wonderful tool! Makes looking at the CDS data a breeze. Question though, is the UNION keyword implemented in the tool? I am not able to make it work and I wonder if there is another way of getting the same thing, a bug in the tool, or simply not implemented. Thanks in advance for any comment.

    Reply

    1. The tool at the moment converts your SQL to a single FetchXML query, and there’s no UNION equivalent in FetchXML so it’s not currently possible. I’m working on a complete overhaul of the conversion process though and UNION is one of the things it will include – see https://twitter.com/MarkMpn/status/1366379432873766915 for an example.

      I also noticed you mentioned about aliasing columns. You can use a query like:

      SELECT name AS name_of_account
      FROM account

      but you can’t do:

      SELECT name AS [name of account]
      FROM account

      Again, this is because it tries to push the aliasing of the column down to the FetchXML, and FetchXML doesn’t support aliases with spaces. This should be another limitation that can be overcome by the new engine.

      Reply

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.