sp_serveroption (Transact-SQL)sp_serveroption (Transact-SQL)

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

为远程服务器和链接服务器设置服务器选项。Sets server options for remote servers and linked servers.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

sp_serveroption [@server = ] 'server'   
      ,[@optname = ] 'option_name'       
      ,[@optvalue = ] 'option_value' ;  

参数Arguments

[ @server = ] 'server' 是要为其设置选项的名称。[ @server = ] 'server' Is the name of the server for which to set the option. server 的数据类型为 sysname,无默认值。server is sysname, with no default.

[ @optname = ] 'option_name' 是要为指定的服务器设置的选项。[ @optname = ] 'option_name' Is the option to set for the specified server. option_namevarchar ( 35 ) ,无默认值。option_name is varchar( 35 ), with no default. option_name可以是以下值之一。option_name can be any of the following values.

Value 描述Description
排序规则兼容collation compatible 影响分布式查询在链接服务器上的执行。Affects Distributed Query execution against linked servers. 如果此选项设置为 ,则返回 trueSQL ServerSQL Server假定链接服务器中的所有字符与字符和排序规则 (或排序顺序) 的本地服务器兼容。If this option is set to true, SQL ServerSQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). 这使 SQL ServerSQL Server 得以将字符列上的比较发送给提供程序。This enables SQL ServerSQL Server to send comparisons on character columns to the provider. 如果没有设置该选项,则 SQL ServerSQL Server 将始终在本地进行字符列上的比较。If this option is not set, SQL ServerSQL Server always evaluates comparisons on character columns locally.

只有在确信链接服务器所对应的数据源与本地服务器有相同的字符集和排序顺序时,才应当设置该选项。This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.
排序规则名称collation name 指定如果使用远程数据源的排序规则名称使用远程排序规则true并且数据源不SQL ServerSQL Server数据源。Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL ServerSQL Server data source. 此名称必须是 SQL ServerSQL Server支持的排序规则之一。The name must be one of the collations supported by SQL ServerSQL Server.

如果访问的是 SQL ServerSQL Server以外的 OLE DB 数据源,但该数据源的排序规则与 SQL ServerSQL Server 的某个排序规则匹配,则使用该选项。Use this option when accessing an OLE DB data source other than SQL ServerSQL Server, but whose collation matches one of the SQL ServerSQL Server collations.

链接服务器必须支持该服务器中所有列使用的单个排序规则。The linked server must support a single collation to be used for all columns in that server. 如果链接服务器支持单个数据源内的多个排序规则,或者如果无法确定链接服务器的排序规则是否与 SQL ServerSQL Server 的某个排序规则匹配,则不要设置该选项。Do not set this option if the linked server supports multiple collations within a single data source, or if the linked server's collation cannot be determined to match one of the SQL ServerSQL Server collations.
连接超时值connect timeout 连接到链接服务器的超时 valuein 秒。Time-out valuein seconds for connecting to a linked server.

如果0,使用sp_configure默认值。If 0, use the sp_configure default.
数据访问data access 启用和禁用链接服务器以进行分布式查询访问。Enables and disables a linked server for distributed query access. 可用于仅sys.server通过添加条目sp_addlinkedserverCan be used only for sys.server entries added through sp_addlinkedserver.
distdist 分发服务器。Distributor.
惰性架构验证lazy schema validation 确定是否检查远程表的架构。Determines whether the schema of remote tables will be checked.

如果 ,则返回 true,跳过远程表的查询的开始处的架构检查。If true, skip schema checking of remote tables at the beginning of the query.
pubpub 发布服务器。Publisher.
查询超时值query timeout 链接服务器上的查询超时值。Time-out value for queries against a linked server.

如果0,使用sp_configure默认值。If 0, use the sp_configure default.
rpcrpc 从给定的服务器启用 RPC。Enables RPC from the given server.
rpc outrpc out 对给定的服务器启用 RPC。Enables RPC to the given server.
subsub 订阅服务器。Subscriber.
systemsystem 标识为仅供参考。Identified for informational purposes only. 不支持。Not supported. 不保证以后的兼容性。Future compatibility is not guaranteed.
使用远程排序规则use remote collation 确定是使用远程列的排序规则还是使用本地服务器的排序规则。Determines whether the collation of a remote column or of a local server will be used.

如果 ,则返回 true,使用远程列的排序规则SQL ServerSQL Server数据源和排序规则中指定排序规则名称用于非SQL ServerSQL Server数据源。If true, the collation of remote columns is used for SQL ServerSQL Server data sources, and the collation specified in collation name is used for non-SQL ServerSQL Server data sources.

如果false,分布式的查询将始终使用本地服务器的默认排序规则时排序规则名称和远程列的排序规则将被忽略。If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. 默认值为 falseThe default is false. ( False值是在中使用的排序规则语义兼容SQL ServerSQL Server7.0。)(The false value is compatible with the collation semantics used in SQL ServerSQL Server 7.0.)
远程过程事务升级remote proc transaction promotion 使用该选项可通过 MicrosoftMicrosoft 分布式事务处理协调器 (MS DTC) 事务保护服务器到服务器的操作过程。Use this option to protect the actions of a server-to-server procedure through a MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC) transaction. 如果此选项是 TRUE (或上) 调用远程存储的过程启动分布式的事务,并用 MS DTC 登记该事务。When this option is TRUE (or ON) calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. 调用远程存储过程的 SQL ServerSQL Server 实例是事务创建者,负责控制事务的完成。The instance of SQL ServerSQL Server making the remote stored procedure call is the transaction originator and controls the completion of the transaction. 当为连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,主控实例请求 MS DTC 在所涉及的计算机间管理分布式事务的完成。When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the connection, the controlling instance requests that MS DTC manage the completion of the distributed transaction across the computers involved.

在启动 Transact-SQLTransact-SQL 分布式事务后,可以对已定义为链接服务器的 SQL ServerSQL Server 实例调用远程存储过程。After a Transact-SQLTransact-SQL distributed transaction has been started, remote stored procedure calls can be made to other instances of SQL ServerSQL Server that have been defined as linked servers. 链接服务器全部登记在 Transact-SQLTransact-SQL 分布式事务中,而 MS DTC 确保对每台链接服务器完成该事务。The linked servers are all enlisted in the Transact-SQLTransact-SQL distributed transaction, and MS DTC ensures that the transaction is completed against each linked server.

如果此选项设置为 FALSE(或 OFF),则对链接服务器调用远程存储过程时将不会把本地事务提升为分布式事务。If this option is set to FALSE (or OFF), a local transaction will not be promoted to a distributed transaction while calling a remote procedure call on a linked server.

如果进行服务器对服务器过程调用前,事务已是分布式事务,则该选项不起作用。If before making a server-to-server procedure call, the transaction is already a distributed transaction, then this option does not have effect. 对链接服务器进行的过程调用将在同一分布式事务下运行。The procedure call against linked server will run under the same distributed transaction.

如果进行服务器对服务器过程调用前,连接中不存在活动事务,则该选项不起作用。If before making a server-to-server procedure call, there is no transaction active in the connection, then this option does not have effect. 然后,将对没有活动事务的链接服务器运行此过程。The procedure then runs against linked server without active transactions.

该选项的默认值为 TRUE(或 ON)。The default value for this option is TRUE (or ON).

[ @optvalue = ] 'option_value' 指定是否option_name应启用 (TRUE) 还是禁用 (FALSE关闭).[ @optvalue = ] 'option_value' Specifies whether or not the option_name should be enabled (TRUE or on) or disabled (FALSE or off). option_valuevarchar ( 10 ) ,无默认值。option_value is varchar( 10 ), with no default.

option_value可能是一个非负整数来连接超时查询超时选项。option_value may be a nonnegative integer for the connect timeout and query timeout options. 有关排序规则名称选项, option_value可能是排序规则名称或 NULL。For the collation name option, option_value may be a collation name or NULL.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

如果排序规则兼容选项设置为 TRUE,排序规则名称自动将设置为 NULL。If the collation compatible option is set to TRUE, collation name automatically will be set to NULL. 如果排序规则名称设置为非 null 值,排序规则兼容自动将设置为 FALSE。If collation name is set to a nonnull value, collation compatible automatically will be set to FALSE.

权限Permissions

要求对服务器拥有 ALTER ANY LINKED SERVER 权限。Requires ALTER ANY LINKED SERVER permission on the server.

示例Examples

以下示例配置与另一个 SQL ServerSQL Server 实例(即 SEATTLE3)相对应的链接服务器,使其排序规则与本地 SQL ServerSQL Server 实例兼容。The following example configures a linked server corresponding to another instance of SQL ServerSQL Server, SEATTLE3, to be collation compatible with the local instance of SQL ServerSQL Server.

USE master;  
EXEC sp_serveroption 'SEATTLE3', 'collation compatible', 'true';  

请参阅See Also

分布式查询存储的过程(Transact SQL) Distributed Queries Stored Procedures (Transact-SQL)
sp_adddistpublisher (TRANSACT-SQL) sp_adddistpublisher (Transact-SQL)
sp_addlinkedserver (Transact-SQL) sp_addlinkedserver (Transact-SQL)
sp_dropdistpublisher (TRANSACT-SQL) sp_dropdistpublisher (Transact-SQL)
sp_helpserver (Transact-SQL) sp_helpserver (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)