Delen via

sp_serveroption (Transact-SQL)

  • Artikel

Sets server options for remote servers and linked servers.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • [ @server = ] 'server'
    Is the name of the server for which to set the option. server is sysname, with no default.

  • [ @optname = ] 'option_name'
    Is the option to set for the specified server. option_name is varchar(35), with no default. option_name can be any of the following values.

    Value Description

    collation compatible

    Affects Distributed Query execution against linked servers. If this option is set to true, SQL Server 2005 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). This enables SQL Server to send comparisons on character columns to the provider. If this option is not 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.

    collation name

    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 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. 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 Server collations.

    connect timeout

    Time-out value in seconds for connecting to a linked server.

    If 0, use the sp_configure default.

    data access

    Enables and disables a linked server for distributed query access. Can be used only for sys.server entries added through sp_addlinkedserver.

    dist

    Distributor.

    lazy schema validation

    Determines whether the schema of remote tables will be checked.

    If true, skip schema checking of remote tables at the beginning of the query.

    pub

    Publisher.

    query timeout

    Time-out value for queries against a linked server.

    If 0, use the sp_configure default.

    rpc

    Enables RPC from the given server.

    rpc out

    Enables RPC to the given server.

    sub

    Subscriber.

    system

    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.

    If 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.

    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. The default is false. (The false value is compatible with the collation semantics used in SQL Server 7.0.)

  • [ @optvalue =] 'option_value'
    Specifies whether or not the option_name should be enabled (TRUE or on) or disabled (FALSE or off). option_value is varchar(10), with no default.

    option_value may be a nonnegative integer for the connect timeout and query timeout options. For the collation name option, option_value may be a collation name or NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

If the collation compatible option is set to TRUE, collation name automatically will be set to NULL. If collation name is set to a non NULL value, collation compatible automatically will be set to FALSE.

Permissions

Requires ALTER ANY LINKED SERVER permission on the server.

Examples

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.

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

See Also

Reference

Distributed Queries Stored Procedures (Transact-SQL)
sp_adddistpublisher (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_dropdistpublisher (Transact-SQL)
sp_helpserver (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Removed the dpub option from the list of valid option_name values.