Forum Discussion
Dataverse query execution in direct query mode
Hi Priyal
Based on the description provided by the error code, it appears that you are experiencing a query execution issue that is causing the connection to fail. Please try the following steps:
DirectQuery mode can be sensitive to complex queries, especially when multiple calculations are involved. Consider simplifying DAX measures or reducing the number of columns in the table.
Verify if there are any known issues or service outages that may affect the Power BI or Dataverse service by checking the Power BI support page and Microsoft 365 service health.
Error code 40197 usually indicates a temporary failure in SQL Server. While this is not specific to Power BI, it may be related to the way DirectQuery performs queries against the Dataverse.
You can check the following links to see if any apply to your situation. Here are some details about transient failures and recommended strategies for handling them:
Working with transient errors - Azure SQL Database | Microsoft Learn
Make sure there are no issues connecting to the Dataverse environment and that you have the necessary permissions.
Gradually increase the complexity of your report by adding one measure or column at a time. This can help determine if a specific metric or query is causing the problem.
Make sure you don't exceed any limits for Power BI and DirectQuery, such as the maximum number of concurrent connections.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
I created the report using a direct query mode with table visual and only one card still got a 40197 error. A calendar table is used in the report for the date range filter. The report worked properly with five years of calendar data; however, it started throwing errors when the calendar table's data size reached ten years. There was no change in the table visual data size; only the calendar data size was increased. So, it must be due to the amount of data processing at runtime. I also checked the timeout settings at Dataverse but didn't find anything. Could you please suggest the possible fixes for this error?
Error log:
DataSource.Error: Microsoft SQL: [ccon] Error occured during query execution. Error code 40197.
DataSourceKind = CommonDataService. DataSourcePath = xxxx.xxx.dynamics.com. Message = "Error occured during query execution. Error code 40197.