5
Good morning everyone!!!
We are looking at the possibility of querying Dataverse/Dynamics 365 data via SQL Server.
I saw that several SYS tables are enabled (eg sys.tables), but some are not (eg sys.partitions).
Well, could someone explain how to query the size of DBO schema tables via SQL Server?
Thanks again PerezAguiar but my major not completed!!!
I opened this ticket as per your suggestion:
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.
Thank you for your attention PerezAguiar
But it does not serve the purpose of querying this information via SQL Server...
I believe there is a possibility but we haven't figured it out yet.
I need to do specific queries on the columns
(Eg SELECT colA, colB FROM Table) and the major
I also need to consult the SYS tables to know the size of each table.
Hi,
You can use Power platform admin center to analyze your entity/table size.
Please mark my answer verified if this is helpful!
Regards,
Bipin Kumar
Follow my Blog: xrmdynamicscrm.wordpress.com/
Hey Anderson.
Unfortunately that's not an allowed action. You can connect to Dataverse using sql server and perform regular queries to undersand values of records. However most of the functionalities (like storage calculation) are outside of the scope, as well as injecting/updating data using this method.
On this article Tabular Data Stream (TDS) Protocol endpoint for Common Data Service (CDS) | Microsoft Power Apps you have a statement from Microsoft's Development group indicating that only Read actions are allowed. On Use SQL to query data (Microsoft Dataverse) - Power Apps | Microsoft Docs you have the lis of allowed operations
Regards
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,552
Super User 2024 Season 2
#2
Martin Dráb
228,552
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (