SQL 4 CDS allows you to use standard SQL syntax to query and manipulate your data and metadata in Dataverse / D365.
Supports - SELECT - INSERT - UPDATE - DELETE
Where possible the queries are converted to FetchXML, allowing you to generate FetchXML queries for plugins or integrations by writing familiar SQL and converting it.
Queries can also run using the preview TDS Endpoint. A wide range of SQL functionality is also built in to allow running queries that aren't directly supported by either FetchXML or the TDS Endpoint.
Latest version release notes
Added Common Table Expression support: - Non-recursive CTEs are expanded to subqueries for compatibility with TDS Endpoint - Recurve CTEs are converted to hierarchical FetchXML filters where possible
Extended JSON support: - OPENJSON table-valued function - JSON_QUERY function - ISJSON function
Query optimizer improvements: - Prefer hash joins over merge joins if sorts cannot be folded - Switch FetchXML sorts to custom sorting after adding joins that require custom paging - Avoid folding filters to tables in subqueries if the same alias exists in the outer query - Do not use a left outer join to implement `NOT IN` queries where the subquery uses an inner join
Added IGNORE_DUP_KEY query hint to ignore duplicate key errors on insert Added check for multi-currency issues when aggregating non-base currency fields Clearer progress messages for multi-threaded DML operations Added autocomplete literal value suggestions for entityname attributes
Fixed inconsistent display of plugin log messages in error message output Fixed use of `UNION` with wildcard columns Fixed error in nested loop joins with no records from inner source Fixed use of columns from outer queries in join criteria in subqueries Fixed time zone mismatch when starting bulk delete jobs Fixed setting polymorphic lookup fields using TDS Endpoint Fixed aggregates with very dense data distribution Preserve comments when formatting query
This is an absolutely beautifully made and essential tool. Absolute nr 1 for me.
Jamie Swindall ()
Love this tool. I don't know how I survived without it before.
Karol Malyszka ()
Brilliant tool.
The only thing missing is support for virtual tables
Regards
Tool author answer ()
Thanks Karol. It should also work with virtual tables, could you add some details of what restrictions you’re seeing at https://github.com/MarkMpn/Sql4Cds/issues/new please?
Mark r ()
cte cant use values, hopefully that will be fixed soon
other than that, a great tool
Hoang Ha ()
yasuyo oomori ()
征宇 蔡 ()
Other versions
Marc Scheuner () - version 7.6.1
Absolutely fantastic tool, saved my day so many times over - can't imagine to work and develop without this in my digital toolbelt!
Walter Diaz () - version 7.6.1
It doesn´t work for my subquery. It's a great application though.
I'll write down my subquery in case yo care about for future improvements:
SELECT c.fullname, c.contactid, t.axn_tipoultimatransaccioncombustible, max(t.createdon)
FROM contact as c
INNER JOIN axn_transaccion as t ON t.axn_contacto=c.contactid
WHERE t.createdon IN (
SELECT MAX(createdon)
FROM axn_transaccion
WHERE axn_tipotransaccion=282450002 AND axn_contacto=c.contactid
) AND c.axn_actualizaronesignal=1 AND t.axn_tipotransaccion=282450002
group by c.fullname, c.contactid, t.axn_tipoultimatransaccioncombustible
Tool author answer ()
Can you give me any more information about the problem you get - does the query fail with an error message, or do you not get the results you expect? If you could open an issue with the details at https://github.com/MarkMpn/Sql4Cds/issues/new I can try to make sure it's fixed in a future update.
CRM ADMIN () - version 7.6.0
Claudeci Especamilha () - version 7.1.0
Stefan Rauchegger () - version 7.1.0
great! easier to use than FetchXmlBuilder and a great starting point to convert queries for FetchXmlBuilder, which then allows you to export select, expand statements for use in power automate
Praveen Thonda () - version 7.0.0
JSON_VALUE is not working when there is space in path eg: JSON_VALUE(description,'$.PipeData.First Name') -- to make it work I had to use the square brackets and escaped single quotes to wrap the key name. JSON_VALUE(description,'$.PipeData.[''First Name'']') - note First Name is wrapped in escaped single quote on either side.
Tool author answer ()
For keys with spaces please use the bracket notation:
JSON_VALUE(description, '$.PipeData[''First Name'']')
(note the double-escaped quote marks around "First Name")
Support for keys containing spaces using the dot-notation seems to vary between libraries. SQL 4 CDS is using the Newtonsoft JSON library which requires the use of the bracket notation in this case - see https://www.newtonsoft.com/json/help/html/QueryJsonSelectTokenEscaped.htm for further examples.
Rayan Hanoudi () - version 6.3.0
I love this tool. I recently used to updated data in D365 and it worked perfectly. Thank you!
Maciek Letowt () - version 6.3.0
Seshendra A () - version 6.2.1
Wonderful tool, helps in my daily job activity. thank you
Tiklu Ganguly () - version 5.4.1
Absolutely awesome tool. love it
Maciek Letowt () - version 5.4.1
Indispensable tool for anyone coming from SQL or DB2 dev background
Joe Bonomo () - version 5.4.1
One of best plugins in the toolbox.
Jussi Muilu () - version 5.3.1
Veljko Vidovic () - version 5.3.1
Nicolas Plourde () - version 5.3.1
Amazing tool! I use it everyday.
ksn choi () - version 5.2.3
RYAN KEAN HO () - version 5.2.2
There's some issues that unable to read user password.
Tool author answer ()
Thanks for letting me know, can you please try again on 5.2.3?
ineke vanarnhem () - version 5.2.1
RYAN KEAN HO () - version 5.2.1
Snitika Luthra () - version 5.1.1
Thankyou so much Mark. I really missed using SQL. You have made my work so much easier. Keep it up!!
Ron Scheuman () - version 5.1.0
This is a fantastic tool! It allows me to very easily inspect and pull data from Dataverse (CDS). Because I'm already familiar with SQL, It allows me to build joins between tables and more complex views very quickly and then view the FetchXML that it generates using FetchXML Builder.
Rafael Gómez () - version 5.0.0
Me ha sido de mucha ayuda. Aún no utilizo las mejoras de la última versión. Pero al querer actualizar datos de la entidad de contactos en productivo me ha marcado un error. El tds está desactivado pero en la configuración estaba habilitado. Ahora lo he desactivado de la configuración e igualmente me ha marcado error después de varios registros actualizados. El problema no es que marque error. Sino que menciona ver la pestaña de detalles. Pero solo se muestra unos gráficos de ejecución. No sé que significan los porcentajes. Y estaría fantástico que pusieras el texto del error, si fue de conexión, si fue por tiempo de espera, si fue por que son muchos datos o hilos. No sé algo más detallado que uno pudiera identificar y entender que anda mal para corregir o evitar que marque errores. Y un tema adicional, veo que tarda mucho tiempo la actualización, entiendo porque se convierte a fetchxml y luego se envía. Hay manera de actualizar más rápido? Actualmente tuve que configurar lotes de 100 a 1 hilo porque a 10 hilos me seguía marcando error. Estoy actualizando 1 campo de contacto, pero haciendo un join a cuenta para filtrar. Saludos y gracias por tu aporte!
Tool author answer ()
Thanks for these details Rafael! Are you able to send me a screenshot of what you see when you get the error please? You can add it to an issue on GitHub or send it to me as a message on Twitter, LinkedIn or at sql4cds@markcarrington.dev
If you're seeing slow update speeds, this is often due to plugins firing on the update process. You may need to check what plugins are running and disable ones that aren't appropriate or limit which attributes they are triggered on.
German Velez () - version 4.0.3
This should a 4.9 but there is no such thing so I gave a 5 anyway but I consider the tool it is 95% perfect for my needs. The other 5% deals with the ability to alias columns but it only lets me name strings with aliases such "select 'Y' as [Yes or No] " but the "select table.Name as [Name of Row]" does not work. The other limitation (for my needs) is the "union" keyword which does not work either. Maybe this is something that does not translate well to FetchXml and thus the tool cannot handle it but I wish it could. :(
Tool author answer ()
Thanks for the feedback! Aliases with spaces and UNION aren't supported in FetchXML and so don't work in this version of the tool, but aliases without spaces (e.g. "select table.Name as Name_of_Row") should work fine. The next version I'm working on should be more flexible at handling things you can't do in FetchXML, so look out for version 5!
Ivo Bouwman () - version 3.1.0
This one helped me a million times by now.
Chung Lee () - version 2.3.0
Marc Scheuner () - version 2.1.2
Absolutely fantastic plugin for anyone who's at home in SQL, and misses the power and flexibility of SQL when working with Dynamics365 - wonderful job, Mark! Couldn't do my work without your plugin!
Durgaprasad Katari () - version 2.1.1
Great tool, can enable TDS endpoint easily without running the orgDBsettings exe.
Nishant Rana () - version 2.0.1
Awesome tool. Thanks !
Patrick Lauziere () - version 1.0.9
Great tool to extract data in fields order you want.
Nicola Micheletto () - version 1.0.7
CRM Admin () - version 1.0.6
Rich Waring () - version 1.0.6
Great to have a way to effectively run sql queries against the D365 back end - saved me a lot of time today.
Related information
Author: Mark CarringtonLatest version: 8.0.0Released on: 11/25/2023Nuget package Id: MarkMpn.SQL4CDSOpen Source: falseGo to project web site Ratings (all versions)5Average rating39Number of ratings Downloads19676Latest version230102All versions3967.28Average per version TagsData
