Skip to content

Latest commit

 

History

History
79 lines (51 loc) · 4.43 KB

configure-the-remote-query-timeout-server-configuration-option.md

File metadata and controls

79 lines (51 loc) · 4.43 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Server configuration: remote query timeout
Learn about the remote query timeout option. See how it determines the number of seconds that a remote operation can take before SQL Server times out.
rwestMSFT
randolphwest
10/18/2024
sql
configuration
conceptual
time limit for remote queries [SQL Server]
remote query timeout option

Server configuration: remote query timeout

[!INCLUDE SQL Server]

This article describes how to configure the remote query timeout server configuration option in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

The remote query timeout option specifies how long, in seconds, a remote operation can take before [!INCLUDE ssNoVersion] times out. The default value for this option is 600, which is a 10-minute wait. Setting this value to 0 disables the time-out. This value applies to an outgoing connection initiated by the [!INCLUDE ssDE] as a remote query. This value has no effect on queries received by the [!INCLUDE ssDE]. A query waits until it completes.

For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.

For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote EXEC statement before the remote stored procedure times out.

Note

The remote query timeout server configuration setting is unrelated to connection and query time-out errors. For more information, see Troubleshoot query time-out errors.

Prerequisites

Remote server connections must be allowed before this value can be set.

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Use SQL Server Management Studio

  1. In Object Explorer, right-click a server and select Properties.

  2. Select the Connections node.

  3. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for [!INCLUDE ssNoVersion] to wait before timing out.

Use Transact-SQL

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote query timeout option to 0 to disable the time-out.

    USE master;
GO

EXECUTE sp_configure 'remote query timeout', 0;
GO

RECONFIGURE;
GO

For more information, see Server configuration options.

Follow up: After you configure the remote query timeout option

The setting takes effect immediately without restarting the server.

Related content