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 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!
SQL 4 CDS is absolute magic! I could cry tears of joy right now!
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
Hi Helena, do you get this error when trying to run a particular query, or just when opening the tool?
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.
FWIW, I am also (still) running into this issue.
Have you updated to the latest XrmToolBox version?
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?
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!
Thanks for this awesome tool.
Is there anywhere that I can report a bug?
I am getting NullReferenceException when I run the Update command.
https://imgur.com/a/zIlPsm0
Thanks for the details Linn, I’ll take a look!
Thanks a ton for this tool Mark. You are an absolute legend.
It is so convenient to check certain things with ease.
Thanks Salman!