Today, we have a discussion with our customer about the Remote Query timeout property and Application Command Timeout. Following, I would like to give some details about this discussion.
First of all, we need to understand that Remote Query Timeout just only applies when SQL SERVER initiated a connection, for example, running an execution from Linked Server, for exampple,
I have the following stored procedure TSQL:
CREATE PROC [dbo].[usp_Timeout]
as
select 1
waitfor delay '00:00:10'
I'm going to update Remote Query Timeout of 5 seconds
EXEC sp_configure 'remote query timeout', 5 ;
GO
RECONFIGURE ;
When I execute the stored procedure I'm going to have EXEC LinkedServer.DatabaseName.dbo.[usp_Timeout], I'm going to have the following error: OLE DB provider "SQLNCLI11" for linked server "LinkedServer" returned message "Query timeout expired".
In this case, you need to change the value of the parameter, we were able to complete the process.
It is important, that in Azure SQL Database this parameter is not supported but it is supported in Azure SQL Managed Instance.
In another hand, remember that this parameter only applies when SQL Server initiated the connection, if you need to increase the command execution timeout from application side, every client (.NET application, Java, PHP, etc.. have a parameter to increase this value, because, the execution is initiated from the client application.
Enjoy!
Microsoft
