SQL Server - Timeout When Executing a Query on a Linked Server

Hey guys,
Good morning.

Today I bring you another quick post about a problem I encountered while trying to run an XMLA script for processing Microsoft Cubes (this happens with T-SQL queries too), where the connection reached a timeout (10 minutes) and was closed by the server with the following message:
A solicitação XML for Analysis expirou antes de ser concluída (The XML for Analysis request timed out before it was completed)

My first reaction was to analyze the LinkedServer properties to increase the timeout, but the 2 parameters were already marked as 0, that is, without timeout. Correct?

Wrong! Consulting the Microsoft manual, we can observe the following definition for these two parameters:

Connection Timeout
Time-out value in seconds for connecting to a linked server.
If 0, use the sp_configure default remote query timeout Option value.

Query Timeout
Time-out value in seconds for queries against a linked server.
If 0, use the sp_configure default query wait Option value.

In other words, if these parameters are defined as 0 (zero), the timeout for queries via Linked Server is NOT infinite, but rather the value of the parameters that are configured in sp_configure, as shown in the print below:

Therefore, if you have a timeout problem like the one I mentioned in this post when executing heavy queries via LinkedServer (for processing XMLA scripts they are necessary), you should change the Linked Server timeout parameters (or change the equivalent parameters in sp_configure, but this could generate side effects on all Linked Servers that use the default values ​​and even on queries that are executed in the database. I do not recommend it)

Increasing this limit to 1h:

Now the cube that I was trying to process via XMLA script instead of manually processing it through Analysis Services itself (now I can automate its processing via T-SQL and Job in SQL Agent) is updating perfectly!

That's it.
Until next time.