Export (0) Print
Expand All
Expand Minimize
0 out of 1 rated this helpful - Rate this topic

sp_addserver (Transact-SQL)

Defines the name of the local instance of SQL Server. When the computer hosting SQL Server is renamed, use sp_addserver to inform the instance of the SQL Server Database Engine of the new computer name. This procedure must be executed on all instances of the Database Engine hosted on the computer. The instance name of the Database Engine cannot be changed.

Topic link icon Transact-SQL Syntax Conventions

sp_addserver [ @server = ] 'server' ,
     [ @local = ] 'local' 
     [ , [ @duplicate_ok = ] 'duplicate_OK' ]
[ @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 that the server that is being added as a local 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. 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 Database Engine is restarted. Only one local server can be defined in each instance of the Database Engine.

[ @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.

0 (success) or 1 (failure)

To set or clear server options, use sp_serveroption.

sp_addserver cannot be used inside a user-defined transaction.

Using sp_addserver to add a remote server is discontinued. Use sp_addlinkedserver instead.

Requires membership in the setupadmin fixed server role.

The following example changes the Database Engine entry for the name of the computer hosting SQL Server to ACCOUNTS.

sp_addserver 'ACCOUNTS', 'local';
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.