Scheduled refresh by gateway failed because of max recursion query

[GijsBI]

Hi, 

I am using a gateway to refresh one of my semantic models. It appears that the scheduled refresh fails for one of my queries due to the maxrecursion limit. I use recursion to duplicate a certain number of rows in my result set. The following code snippet is used at the end of my query to achieve this:

OPTION (MAXRECURSION 0)

Could this be the reason my scheduled refresh fails? I receive the following error from PowerBI Service after it tried to refresh the semantic model: 
"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding". 

Upon reviewing my settings within MSSQL, I noticed that the execution timeout is set to 0, which means it should wait indefinitely until completion. Additionally, when the semantic model is refreshed without this query, it works perfectly. Also, the query executes successfully when I manually refresh the semantic model from Power BI Desktop. Are other people experiencing the same issue? 

[GijsBI]

I also tried to refresh the data with the following code snippet at the end of my query:

OPTION (MAXRECURSION 1000)

This did not solve the issue unfortunately.

[lbendlin]

Upon reviewing my settings within MSSQL, I noticed that the execution timeout is set to 0, which means it should wait indefinitely until completion.

Not really. There are multiple other timeouts that can bite.  SQL Server usually has a fallback timeout of 120 minutes, and Power BI Service has a hard timeout of 300 minutes.

[GijsBI]

Hi Ibendlin, 

Thank you for your response. I didn't know this, so thanks for the information. Unfortunately, this does not solve my issue, since the query is only running for ~10 minutes.