Remote query timeout for Azure SQL Database

0
votes

I'm executing a stored procedure on an Azure SQL database.

The procedure is being called via Linked Server from a SQL Server Agent job on a local SQL Server instance:

EXEC ('EXEC azuredb.dbo.FullProcess') AT [MYAZURESERVER.DATABASE.WINDOWS.NET]

After ca. 12 minutes of execution I get an query timeout error:

OLE DB provider "SQLNCLI11" for linked server "MYAZURESERVER.DATABASE.WINDOWS.NET" returned message "Query timeout expired".

How can I disable/control remote timeout period? I tried setting the "Remote Query Timeout" to 0 in the Linked Server settings, but it doesn't seem to have an effect. Also, there seems to be no such setting on the Azure SQL Server.

1
azure-sql-databaseazure-sql-server
Did you check the firewall rules of the Azure SQL? – Joseph Xu
The firewall rules are correctly configured. This is not the reason for this behaviour - if firewall would be an issue, then I wouldn't be able to run the stored procedure at all. – saso
You can query from any tables in Azure sql via linked server? – Joseph Xu
Yes. The problem is the timeout error that occurs when the execution takes more than ca. 12 minutes. – saso
Hi @saso, Can it work now? – Joseph Xu

1 Answers

1
votes

Update:
My stored procedure will execute for 12 minutes. When I set the "Remote Query Timeout" to 900 or bigger in the Linked Server settings. The "Remote Query Timeout" should be a bit larger than the actual execution time. If the values are the same, the timeout error will still be reported.
enter image description here It successfully returned the result! These two time differences are the execution time of the stored procedure:
enter image description here

Hi @saso My steps are as follows. According to this article, it says we need to create 2 linked servers to achieve that.

  1. Create first linked server named JOSEPHSERVER2.DATABASE.WINDOWS.NET:
    enter image description here Enter login name and password to login on Azure SQL:
    enter image description here

  2. Create second linked server named AZURE LINKED SERVICE. Sceond linked server will reference first linked server. Enter userdb name in Catalog.
    enter image description here Also need to enter login name and password:
    enter image description here Select Server Options and set "RPC" and "RPC Out" as true, so that we can call stored procedure via this linked server:
    enter image description here

  3. Then I can call remote stored procedures in a local SQL Server:
    enter image description here