配置 remote query timeout 服务器配置选项Configure the remote query timeout Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL Server 2019SQL Server 2019 中配置 SQL Server Management StudioSQL Server Management Studio remote query timeout Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the remote query timeout server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. remote query timeout 选项指定在 SQL ServerSQL Server 超时之前远程操作可以持续的时间(秒)。此选项的默认值是 600,即允许等待 10 分钟。The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL ServerSQL Server times out. The default value for this option is 600, which allows a 10-minute wait. 此值将应用到由作为远程查询的 数据库引擎Database Engine 发起的发送连接。This value applies to an outgoing connection initiated by the 数据库引擎Database Engine as a remote query. 此值不会对 数据库引擎Database Engine接收的查询产生任何影响。This value has no effect on queries received by the 数据库引擎Database Engine. 若要禁用该超时,请将此值设置为 0。To disable the time-out, set the value to 0. 查询将一直等待,直到完成。A query will wait until it completes.

对于异类查询, remote query timeout 指定远程访问接口在查询超时前应等待结果集的秒数(由命令对象使用 DBPROP_COMMANDTIMEOUT 行集属性进行初始化)。如果远程提供程序支持该值,则该值还用于设置 DBPROP_GENERALTIMEOUT。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.

对于远程存储过程, remote query timeout 指定在发送一个远程 EXEC 语句之后,在远程存储过程超时前必须等待的秒数。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

  • 在设定此值前,必须允许远程服务器连接。Remote server connections must be allowed before this value can be set.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。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. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置 remote query timeout 选项To configure the remote query timeout option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “连接” 节点。Click the Connections node.

  3. “远程服务器连接” 下的 “远程查询超时值” 框中,键入或选择介于 0 到 2,147,483,647 之间的值以设置 SQL ServerSQL Server 在超时之前等待的最多秒数。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 ServerSQL Server to wait before timing out.

使用 Transact-SQLUsing Transact-SQL

配置 remote query timeout 选项To configure the remote query timeout option

  1. 连接到 数据库引擎Database EngineConnect 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. 此示例说明如何使用 sp_configureremote query timeout 选项的值设置为 0 以禁用超时。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  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置 remote query timeout 选项之后Follow Up: After you configure the remote query timeout option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
行集属性和行为 Rowset Properties and Behaviors
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)