Dataset Refresh Problem

[samgreene1]

Hi,

I have a dataset that is refreshed multiple times per day.  The first refresh of the day is consistently failing.  It fails on different queries each time. The refresh is through our data gateway against a SQL Server data warehouse.  Refreshes from the desktop app always work in a minute or two. 

I have reference information from the error message available. 

Thanks,

Sam

[v-yangliu-msft]

Hi  @samgreene1 ,

For the SQL Server Connector in Power BI, it has a default timeout value limit of 10 minutes, which is mentioned in the official documentation of the Power BI SQL Server Connector. So there will be random errors when the data is refreshed:

Power Query SQL Server connector - Power Query | Microsoft Learn

Please try to set the timeout time for this connection when you connect to SQL Server Database with Power BI Desktop.

This is the related document, you can view this content:

Solved: Microsoft SQL: Execution Timeout Expired Error whi... - Microsoft Fabric Community

Solved: Microsoft SQL: Execution Timeout Expired - Microsoft Fabric Community

Timeout expired messages when connecting to SQL Server - SQL Server | Microsoft Learn

Best Regards,

Liu Yang

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

[samgreene1]

After updating the timeout to 15 minutes, there is still a failure - but only in the morning. 

[GilbertQ · Accepted solution]

Hi @samgreene1 

That then means that there is an issue with the underlying dataset in the morning.

[samgreene1]

Thanks for the suggestion. I have set the timeout to 15 minutes, however, I don't expect this to have a positive result as the report normally takes 1 minute 30 seconds to refresh.  

[samgreene1]

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"11"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"<REDACTED SERVER>;<REDACTED DB>"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"-2"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: ConfigurationItem.

[GilbertQ]

Hi @samgreene1 

If you could post the error message that would be ideal.

Also can you make sure that if you log in from your gateway that it can successfully connect to the SQL Server from the Gateway server?

[samgreene1]

I posted the message.  The gateway tests fine and on-demand refreshes always seem to work as well as refreshes 2-4 of the day.