Do you enable SQL (Read-Only) connect to the Common Data Service?

Last updated by Brook Jeynes [SSW] 10 months ago.See history

Currently in preview (as at May 2020) Common Data Service supports direct SQL Access to Dynamics entities. This is huge for many Dynamics scenarios, in particular using it for direct query Power BI reports.

As this feature is in preview (as at May 2020) you'll need to enable it, and it's not recommended for production environments. With that out of the way follow the steps at the Microsoft Docs page: Use SQL to query data (Preview)

Supported operations and data types

The list of supported SQL operations includes:

Batch operations

  • Aggregation functions (i.e., Count() and Max() functions)
  • UNIONs and JOINs
  • Filtering

Any operation that attempts to modify data (i.e., INSERT, UPDATE) will not work as this is a read-only SQL data connection. Common Data Service option sets are represented as <OptionSet>Name and <OptionSet>Label in a result set.

The following Common Data Service datatypes are not supported with the SQL connection:

  • binary
  • image
  • ntext
  • sql_variant
  • varbinary
  • virtual
  • HierarchyId
  • managedproperty
  • file
  • xml
  • partylist
  • timestamp
We open source. Powered by GitHub