Query CDS with SQL!

If you’re anything like me you probably find querying the data in CDS a pain, either using the Advanced Find interface or writing FetchXml. Tools such as FetchXMLBuilder help a lot, but I still think about the query I want to write in good-old SQL and then have to translate it into FetchXML.

To make my life easier I created a tool to do the translation for me, and now I’ve released it as an XmlToolBox tool for you to use too – SQL 4 CDS!

Write and execute CDS queries as SQL

Write your query as SQL and either execute it straight away or see it converted to FetchXml for you to use elsewhere.

Please give it a go and let me know any feedback!

Join the conversation

8 Comments

  1. I cannot use your SQL4CDS. I receive the following error message “unable to read user password”. I am connected to the dynamics 365 org online. I cannot understand which user password was unable to read.

    Best regards and thanks for your help
    Helena

    1. OK, I’ve managed to reproduce this when you connect to an online instance using the SDK Login control rather than the Connection Wizard. Not sure why this would be, I’ll look into it.

  2. First, thank you for this great XrmToolBox tool.
    Second, do you know when the tool will provide date calculations? For example, I can’t use the “dateadd” function to calculate date differences. Or, if I want to show records older than 6 months, how could I do this using your tool?

    1. Hi Aaron, apologies for the delay, I’m not used to getting comments on my blog and all the notifications just went to spam!

      There are two ways of getting the results you want. Firstly, for a simple fixed point in time you can do:

      SELECT name FROM account WHERE createdon < '2019-05-12'

      For a more Dynamic(tm) query you can also use the more advanced FetchXML operators, but to make them usable in SQL I had to change the syntax slightly:

      SELECT name FROM account WHERE createdon = olderthanxmonths(6)

      Hope that helps!

Leave a comment

Your e-mail address will not be published. Required fields are marked *