Hey Anderson.
1) you can perform the query using the regular query structure:
SELECT act.activityid, act.subject, string_agg([to].partyidname, ', ')
FROM activitypointer AS act
LEFT OUTER JOIN activityparty as [to] ON act.activityid = [to].activityid and [to].participationtypemask = 2
GROUP BY act.activityid, act.subject
This is a valid SqlQuery structure and will return values. However, as indicated previously, on docs.microsoft.com/.../dataverse-sql-query there are some limitations:
- 80MB of data of resuls.
- Dates are returned in UTC
- Plugins are not triggered
- They count on the API protection Limits
ON docs.microsoft.com/.../how-dataverse-sql-differs-from-transact-sql you have a list of Supported and NotSupported operations (as Dataverse allows some of SQL commands but not fully). Samples of stuff that won't work:
- Stored procedures
- Security
- Cursors
- Execute
You also have other non suported functions (like Col_Name, Col_length, ColumnProperty, DatabasePropertyEx, DB_ID). Basically: operations with system tables are not allowed.
If you need the storage, you need to go to PowerPlatform admin center. Currently, there's no API to retrieve the storage/Data information. YOu can go to experience.dynamics.com/.../ and check if this feature is already requested or submit your own.
