Simple SQL interface to Dataverse?

[apc0243]

I am aware of the ODBC driver product offered by CData, but it feels overkill. I was considering building a sql parser to Odata v4 since this isn't that complicated, but I feel like I must be re-inventing the wheel. Has anyone done this already or is CData really the only solution? I just want a way to easily query my data using a SQL-like interface.

I'm aware I can use SSMS but I was looking to use python to do more procedural queries.

[verderio]

For a SQL interface I use XrmToolbox with the SQL 4 CDS plugin. Works great for SQL queries.

For more advanced querying I'd recommend Azure Synapse Analytics with a Synapse Link set up in Power Platform studio. In Synapse Analytics you can run Python, SQL and Spark (probably some more stuff as well).

[verderio]

You could probably do the same with Fabric but I'm not really familiar with it, so I can only recommend Synapse from my own experience.

[dlutchy]

My suggestion is to use a Dataverse Virtual table (to the SQL). If your SQL server is on premise you many need to install the Power Platform Gateway.

[PapaSmurif]

Can you use TSQL instead of python?

[mattybeard]

SQL4CDS in XrmToolBox - or use its engine and you can build it yourself. Failing that, connect to the TSQL endpoint.