Leverage SQL for querying data from Dynamics 365 Online
- Najm-us-saher Farooque
- Sep 16, 2023
- 2 min read
In reference to my prior post Connect SQL Server to the Dynamics 365 Online database, I previously detailed the procedure for establishing a connection between SQL Server and the Dynamics 365 Online database. This current post serves as an extension of the aforementioned discussion, wherein I will elucidate the process of querying data from SQL Server.

Basic Data Retrieval:
A rudimentary data retrieval operation parallels querying any conventional SQL database. Notably, the nomenclature of SQL table attributes corresponds to the logical names in the Dataverse.

Join Operations:
To execute a join query, you may formulate it as follows. This join operation furnishes results pertaining to Accounts and their respective Primary Contacts. It is noteworthy that Contacts and Accounts are distinct tables within the Dataverse.

Activity Parties:
An activity party denotes an individual or group associated with an activity. Multiple activity parties can be linked to a single activity.
For an in-depth exploration of Activity Party, please consult the following blog: Activity Party Documentation.
Partylist attributes can be queried by engaging the activityparty table, as demonstrated below. In this query, we retrieve all activities related to the activity party, with the Activity Party being represented by a distinct table. The activity party type is stored as an integer value in the ActivityParty.ParticipationTypeMask attribute. Activity Parties with ParticipationTypeMask equal to 2 are targeted in this query.

Lookup and Optionset Queries:
Lookup columns are determined by the lookup column ID, which represents a unique identifier in the lookup table. Optionset values, on the other hand, are ascertained by their corresponding optionset values. The ensuing query showcases the retrieval of Associated Accounts from Contacts using the account ID (accountid) and Customer Type (customertypecode), which is an option set.

Update Queries:
It is imperative to note that update queries cannot be executed directly in the SQL Server Database of Dataverse, as the database is configured as read-only.
However, you can utilize the SQL 4 CDS Utility in XRMToolBox to execute updates on Dataverse through SQL queries.
Furthermore, SQL queries can be converted into FETCHXML for utilization in SSRS Reports. Additional information about the SQL 4 CDS Utility can be found here.
As an illustrative example, suppose there exists a company named AB Company, and you intend to alter its name to ABC Company through an SQL Query. The SQL query would facilitate the change in the Account's name.


Additionally, the SQL 4 CDS utility allows for the retrieval of audit logs, a feature not available when executing an update command from an SQL Database in an on-premise setting. This utility leverages Dataverse services to provide audit logs.

Please take note of the following limitations when querying Dataverse columns:
There is a maximum size limit of 80 MB for query results returned from the Dataverse endpoint.
Dates returned in query results are formatted in the Universal Time Coordinated (UTC).
It is important to be aware that querying data using SQL does not trigger any plug-ins that may have been registered on the RetrieveMultipleRequest or RetrieveRequest messages.
Tables of types 'virtual' and 'audit' are currently unsupported.
Stay tuned for upcoming blog posts covering topics in Dataverse and Power Apps.
Opmerkingen