Share via


Configure the remote query timeout Server Configuration Option

This topic describes how to configure the remote query timeout server configuration option in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it is canceled.

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.

In This Topic

  • Before you begin:

    Prerequisites

    Security

  • To configure the remote query timeout option, using:

    SQL Server Management Studio

    Transact-SQL

  • Follow Up:  After you configure the remote query timeout option

Before You Begin

Prerequisites

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

Security

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.

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To configure the remote query timeout option

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

  2. Click 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 SQL Server to wait before timing out.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To configure the remote query timeout option

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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 AdventureWorks2012 ;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

For more information, see Server Configuration Options.

Arrow icon used with Back to Top link [Top]

Follow Up: After you configure the remote query timeout option

The setting takes effect immediately without restarting the server.

Arrow icon used with Back to Top link [Top]

See Also

Reference

RECONFIGURE (Transact-SQL)

sp_configure (Transact-SQL)

Concepts

Rowset Properties and Behaviors

Server Configuration Options