sp_addserver (Transact-SQL)

Defines the name of the local instance of SQL Server. This stored procedure also defines a remote server.

Transact-SQL Syntax Conventions

Syntax

sp_addserver [ @server = ] 'server' 
     [ , [ @local= ] 'local' ] 
     [ , [ @duplicate_ok = ] 'duplicate_OK' ]

Arguments

• [ @server = ] 'server'
  Is the name of the server. Server names must be unique and follow the rules for Microsoft Windows computer names, although spaces are not allowed. server is sysname, with no default.

  When multiple instances of SQL Server are installed on a computer, an instance operates as if it is on a separate server. Specify a named instance by referring to server as servername\instancename.

• [ @local = ] 'LOCAL'
  Specifies whether the server that is being added is a local or remote server. @local is varchar(10), with a default of NULL. Specifying @local as LOCAL defines @server as the name of the local server and causes the @@SERVERNAME function to return the value of server.

  SQL Server Setup sets this variable to the computer name during installation. We recommend that the name not be changed. By default, the computer name is the way users connect to an instance of SQL Server without requiring additional configuration.

  The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in each server.

• [ @duplicate_ok = ] 'duplicate_OK'
  Specifies whether a duplicate server name is allowed. @duplicate_OK is varchar(13), with a default of NULL. @duplicate_OK can only have the value duplicate_OK or NULL. If duplicate_OK is specified and the server name that is being added already exists, no error is raised. If named parameters are not used, @local must be specified.

Return Code Values

0 (success) or 1 (failure)

Remarks

To execute a stored procedure on a remote server (remote procedure calls) that is running an earlier version of SQL Server, add the remote server by using sp_addserver. To execute a stored procedure, or any distributed query, on a remote server that is running SQL Server version 7.0 or a later version, use sp_addlinkedserver to add the server.

To set or clear server options, use sp_serveroption.

sp_addserver cannot be used inside a user-defined transaction.

Permissions

Requires membership in the setupadmin fixed server role.

Examples

The following example creates an entry for the remote server ACCOUNTS on the local server.

sp_addserver 'ACCOUNTS';

See Also

Reference

sp_addlinkedserver (Transact-SQL)
sp_addremotelogin (Transact-SQL)
sp_dropremotelogin (Transact-SQL)
sp_dropserver (Transact-SQL)
sp_helpremotelogin (Transact-SQL)
sp_helpserver (Transact-SQL)
sp_serveroption (Transact-SQL)
System Stored Procedures (Transact-SQL)
Security Stored Procedures (Transact-SQL)

Other Resources

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005

Help and Information

Getting SQL Server 2005 Assistance

Change History