2
edits
Changes
Jump to navigation
Jump to search
Created page with "TDS endpoint makes it possible to query your Dataverse data using SQL. You can use Power BI in DirectQuery mode to ensure your reports are using live data instead of perio..."
TDS endpoint makes it possible to query your Dataverse data using [[SQL]]. You can use Power BI in DirectQuery mode to ensure your reports are using live data instead of periodically refreshed data, or you can do bespoke queries from SQL Server Management Studio (SSMS) or your own custom applications.
Although it does not give a direct connection to the underlying SQL database, the connection is made using the same SQL Server protocol (TDS), so client applications such as SSMS do not need to know the difference. Some rewriting/sanitization of the query is done by the TDS endpoint before it is ultimately passed to the SQL database to be executed, so not all SQL features are available. Notably, the database is read-only so only SELECT queries are usable.
You can connect to the TDS endpoint for a Dataverse instance using the same URL, e.g. contoso.crm.dynamics.com. Some examples will show using port 5558 for the connection - this was required at the start of the preview, but you can now connect on the default port 1433 as well.
== Microsoft Resources ==
* [https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query Use SQL to query data]
== Community Resources ==
=== Articles ===
* [https://markcarrington.dev/2020/05/13/cds-t-sql-endpoint-pt-1-connecting/ Connecting to TDS endpoint] by Mark Carrington
* [https://markcarrington.dev/2020/08/04/msdyn365-internals-t-sql-endpoint/ TDS endpoint internals] by Mark Carrington
* [https://markcarrington.dev/2020/08/09/cds-t-sql-performance/ TDS endpoint performance] by Mark Carrington
=== Tools ===
* [https://markcarrington.dev/sql-4-cds/ SQL 4 CDS] by Mark Carrington
[[Category:Developer]]
Although it does not give a direct connection to the underlying SQL database, the connection is made using the same SQL Server protocol (TDS), so client applications such as SSMS do not need to know the difference. Some rewriting/sanitization of the query is done by the TDS endpoint before it is ultimately passed to the SQL database to be executed, so not all SQL features are available. Notably, the database is read-only so only SELECT queries are usable.
You can connect to the TDS endpoint for a Dataverse instance using the same URL, e.g. contoso.crm.dynamics.com. Some examples will show using port 5558 for the connection - this was required at the start of the preview, but you can now connect on the default port 1433 as well.
== Microsoft Resources ==
* [https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query Use SQL to query data]
== Community Resources ==
=== Articles ===
* [https://markcarrington.dev/2020/05/13/cds-t-sql-endpoint-pt-1-connecting/ Connecting to TDS endpoint] by Mark Carrington
* [https://markcarrington.dev/2020/08/04/msdyn365-internals-t-sql-endpoint/ TDS endpoint internals] by Mark Carrington
* [https://markcarrington.dev/2020/08/09/cds-t-sql-performance/ TDS endpoint performance] by Mark Carrington
=== Tools ===
* [https://markcarrington.dev/sql-4-cds/ SQL 4 CDS] by Mark Carrington
[[Category:Developer]]