Applies to: SQL Server
Sets server options for remote servers and linked servers.
[ @server = ] N'server'
, [ @optname = ] 'optname'
, [ @optvalue = ] N'optvalue'
[ ; ]
[ @server = ] N'server'
The name of the server for which to set the option. @server is sysname, with no default.
[ @optname = ] 'optname'
The option to set for the specified server. @optname is varchar(35), with no default. @optname can be any of the following values.
|Affects distributed query execution against linked servers. If this option is set to
true, SQL Server assumes that all characters in the linked server are compatible with the local server, regarding character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option isn't set, SQL 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.
|Specifies the name of the collation used by the remote data source if use remote collation is
true and the data source isn't a SQL Server data source. The name must be one of the collations supported by SQL Server.
Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.
The linked server must support a single collation to be used for all columns in that server. Don't set this option if the linked server supports multiple collations within a single data source, or if the linked server's collation can't be determined to match one of the SQL Server collations.
|Specifies the timeout value in seconds for connecting to a linked server.
0, the connect timeout setting uses the default value that is configured for the
remote login timeout (s) setting. The default value for
remote login timeout (s) is
You can view this setting from the
sys.configurations catalog view with the following query:
SELECT name, value_in_use FROM sys.configurations WHERE name like 'remote login timeout (s)';.
|Enables and disables a linked server for distributed query access. Can be used only for
sys.server entries added through
|Specifies the name of the linked server object.
The name change is reflected in the value returned by the
name column of the
sys.servers catalog view, without affecting the remote data source.
|Specifies the OLE DB string that identifies the source of a linked server connection.
The provider string change is reflected in the value returned by the
provider_string column of the
sys.servers catalog view.
|lazy schema validation
|Determines whether the schema of remote tables is checked.
true, skip schema checking of remote tables at the beginning of the query.
|Specifies the timeout value for queries against a linked server.
0, use the
|Enables RPC from the given server.
|Enables RPC to the given server.
|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 is used.
true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources. This is the default.
false, distributed queries always use the default collation of the local server, while collation name and the collation of remote columns are ignored.
|remote proc transaction promotion
|Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is
on), calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. The instance of SQL Server making the remote stored procedure call is the transaction originator and controls the completion of the transaction. When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the connection, the controlling instance requests that MS DTC manages the completion of the distributed transaction across the computers involved.
After a Transact-SQL distributed transaction has been started, remote stored procedure calls can be made to other instances of SQL Server that have been defined as linked servers. The linked servers are all enlisted in the Transact-SQL distributed transaction, and MS DTC ensures that the transaction is completed against each linked server.
If this option is set to
off), a local transaction isn't 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 has no effect. The procedure call against linked server runs under the same distributed transaction.
If there's no transaction active in the connection before making a server-to-server procedure call, this option has no effect. The procedure then runs against linked server without active transactions.
The default value for this option is
[ @optvalue = ] N'optvalue'
Specifies whether the @optname should be enabled (
on), or disabled (
off). @optvalue is nvarchar(128), with no default.
For the connect timeout and query timeout options, @optvalue may be a non-negative integer.
For the collation name option, @optvalue may be a collation name or
For the name option, @optvalue may be a string, which represents the new name of the linked server connection.
For the provider string option, @optvalue may be a string or
NULL, representing the new OLE DB source of the linked server connection.
Return code values
0 (success) or
If the collation compatible option is set to
true, collation name automatically is set to
If collation name is set to a non-null value, collation compatible automatically is set to
Requires ALTER ANY LINKED SERVER permission on the server.
The following example configures a linked server corresponding to another instance of SQL Server,
SEATTLE3, to be collation compatible with the local instance of SQL Server.
EXEC sp_serveroption N'SEATTLE3', 'collation compatible', N'true';
The following example renames the linked server connection from
@server = N'PRODVM01\ProdSQL01',
@optname = 'name',
@optvalue = N'LinkToProdSQL01';