Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Priyal
Frequent Visitor

Dataverse query execution in direct query mode

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.

2 REPLIES 2
Anonymous
Not applicable

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.

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.