sp_helpserver

Transact-SQL Reference

Transact-SQL Reference

sp_helpserver

Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the server's network name, the server's replication status, the server's identification number, collation name, and time-out values for connecting to, or queries against, linked servers.

Syntax

sp_helpserver [ [ @server = ] 'server' ]
    [ , [ @optname = ] 'option' ]
    [ , [ @show_topology = ] 'show_topology' ]

Arguments

[@server =] 'server'

Is the server about which information is reported. When no server is supplied, reports about all servers in master.dbo.sysservers. server is sysname, with a default of NULL.

[@optname =] 'option'

Is the option describing the server. option is varchar(35), with a default of NULL, and must be one of these values.

Value Description
collation compatible Affects the 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).
data access Enables and disables a linked server for distributed query access.
dist Distributor.
dpub Remote Publisher to this Distributor.
lazy schema validation Skips schema checking of remote tables at the beginning of the query.
pub Publisher.
rpc Enables RPC from the given server.
rpc out Enables RPC to the given server.
sub Subscriber.
system For internal use only.
use remote collation Uses the collation of a remote column rather than that of the local server.

[@show_topology =] 'show_topology'

Is the relationship of the given server to other servers. show_topology is varchar(1), with a default of NULL. If show_topology is not equal to t or is NULL, sp_helpserver returns columns listed in the Result Sets section. If show_topology is equal to t, in addition to the columns listed in the Result Sets, sp_helpserver also returns topx and topy information.

Return Code Values

0 (success) or 1 (failure).

Result Sets
Column name Data type Description
name sysname Server name.
network_name sysname Server's network name.
status varchar(70) Server status.
id char(4) Server's identification number.
collation_name sysname Server's collation.
connect_timeout int Time-out value for connecting to linked server.
query_timeout int Time-out value for queries against linked server.

Remarks

A server can have more than one status.

Permissions

Execute permissions default to the public role.

Examples
A. Display information about all servers

This example displays information about all servers (sp_helpserver with no parameters).

USE master
EXEC sp_helpserver
B. Display information about a specific server

This example displays all information about the SEATTLE2 server.

USE master
EXEC sp_helpserver 'SEATTLE2'

See Also

sp_adddistpublisher

sp_addserver

sp_addsubscriber

sp_changesubscriber

sp_dboption

sp_dropserver

sp_dropsubscriber

sp_helpdistributor

sp_helpremotelogin

sp_helpsubscriberinfo

sp_serveroption

System Stored Procedures