Dataverse query execution in direct query mode

[Priyal]

Hi,

I have created a Power BI report using direct query mode with a Dataverse connection. There are seven cards with DAX measures and one table with 25 columns in the report. I got the below error on the Power BI desktop and service while refreshing the data:

  DataSource.Error: Microsoft SQL: [ccon] Error occurred during query execution. Error code 40197. The exception was raised by the IDbCommand interface.

It refreshed the data when I created the report using an import mode connection. It also refreshed the data when I created the report in direct query mode with one card rather than seven cards.
I set up the Dataverse connection using the SQL server on the default port and increased the query timeout settings to two hours using direct query mode, still got the 40197 error.

[Anonymous]

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.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#directquery-limita...

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Priyal]

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.