Rules

Secret ingredients to quality software

Edit
Info

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

Created on 25 Jun 2020 | Last updated by Tiago Araujo on 25 Jun 2020 10:10 PM (10 months ago)

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

  • SELECT
  • 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.
Adam CoganAdam Cogan
Mehmet OzdemirMehmet Ozdemir

We open source. This page is on GitHub