CODEWITHSUNDEEP
azure-sql-databaseazure-sql-server
saso
saso

Reputation: 802

Remote query timeout for Azure SQL Database

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.

Upvotes: 0

Views: 2039

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6043

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

Upvotes: 1

Related Questions