Hello guys,
Good day.
Today I bring you another quick post of a problem I encountered while trying to run an XMLA script for Microsoft Cube processing (this happens with T-SQL queries too) where the connection timed out (10 minutes) and was terminated 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 LinkedServer properties to increase the timeout, but the 2 parameters were already marked as 0, ie no timeout. Right?
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.
That is, if these parameters are set to 0 (zero), the timeout of queries via Linked Server is NOT infinite, but the value of the parameters that are set in sp_configure, as shown below:
Therefore, if you have a timeout problem like what I commented in this post when running heavy queries via LinkedServer (XMLA scripting is required), you should change the Linked Server timeout parameters (or change the equivalent parameters). in sp_configure, but this could have side effects on all Linked Servers that use default values and even queries that run on the database.
Increasing this limit to 1h:
Now the cube I was trying to process via XMLA script instead of processing by Analysis Services itself manually (now I can automate processing it via T-SQL and Job in SQL Agent) is updating perfectly!
That's it.
To the next.
