設定 remote query timeout 伺服器組態選項Configure the remote query timeout Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此主題描述如何使用 ,在 SQL ServerSQL Server 中設定 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 ServerSQL Server 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 EngineDatabase Engine 啟始做為遠端查詢的傳出連接。This value applies to an outgoing connection initiated by the Database EngineDatabase Engine as a remote query. 此值對 Database EngineDatabase Engine收到的查詢沒有影響。This value has no effect on queries received by the Database EngineDatabase Engine. 若要停用逾時,請將值設定為 0。To disable the time-out, set the value to 0. 查詢會等候,直到完成。A query will wait until it completes.

對於異質性查詢,[遠端查詢逾時] 可指定遠端提供者在等候查詢結果集時,應等候幾秒 (使用 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.

安全性Security

權限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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。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 EngineDatabase EngineConnect to the Database EngineDatabase 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).

後續操作:設定遠端查詢逾時選項之後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)