sp_serveroption (T-SQL)

Sets server options for remote servers and linked servers. Also provides options for distributed queries that can be enabled or disabled on a per-linked-server basis.

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 a default of NULL. If server is omitted, the options that can be set with this stored procedure are displayed.

[@optname =] 'option_name'

Is the option to set for the specified server. option_name is varchar(35), with a default of NULL. If server is specified but option_name is omitted, the options that are set are displayed.

Value	Description
collation compatible	Affects Distributed Query execution against linked servers. If this option is set to true, Microsoft® SQL 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). 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.
data access	Enables and disables a linked server for distributed query access. Can be used only for sysserver entries added through sp_addlinkedserver.
dist	Distributor.
dpub	Remote Publisher to this Distributor.
pub	Publisher.
rpc	Enables RPC from the given server.
rpc out	Enables RPC to the given server.
sub	Subscriber.
system	For internal use only.

[@optvalue =] 'option_value'

Is whether or not the specified option_name should be enabled (TRUE or on) or disabled (FALSE or off). option_value is varchar(10), with a default of NULL. If option_value is NULL, the current setting for the specified option is displayed.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If no parameters are specified, this is the result set.

Column name	Data type	Description
server_option	nvarchar(35)	Lists server options that can be set on or off.

If server is specified, this is the result set.

Column name	Data type	Description
The following options are set:	nvarchar(35)	Lists server options that are currently enabled for server.

If server and option_name are specified, this is the result set.

Column name	Data type	Description
OptionName	nvarchar(35)	Lists specified option_name.
CurrentSetting	char(3)	Shows current setting for option_name, on or off.

If option_value is specified, sp_serveroption returns no result set.

Permissions

Execute permissions default to the public role; however, only members of the setupadmin server role can set server options.

Examples

A. Enable a Publisher/Subscriber server

This example sets the server as a combination Publisher/Subscriber server.

USE master

EXEC sp_serveroption 'ACCOUNTS', 'dpub', 'TRUE'

B. Disable a distribution server

This example turns off the dist option for the SEATTLE2 server.

USE master

EXEC sp_serveroption 'SEATTLE2', 'dist', 'off'

C. Enable a linked server to be collation compatible with a local SQL Server

This example configures a linked server corresponding to another SQL Server, SEATTLE3, to be collation compatible with the local SQL Server.

USE master

EXEC sp_serveroption 'SEATTLE3', 'collation compatible', 'true'

sp_addlinkedserver	sp_helpserver
sp_adddistpublisher	System Stored Procedures
sp_dropdistpublisher