| title | sp_addserver (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 06/10/2016 | ||||
| ms.prod | sql-non-specified | ||||
| ms.reviewer | |||||
| ms.suite | |||||
| ms.technology |
|
||||
| ms.tgt_pltfrm | |||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 160a6b29-5e80-44ab-80ec-77d4280f627c | ||||
| caps.latest.revision | 40 | ||||
| author | BYHAM | ||||
| ms.author | rickbyh | ||||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Defines the name of the local instance of [!INCLUDEssNoVersion]. When the computer hosting [!INCLUDEssNoVersion] is renamed, use sp_addserver to inform the instance of the [!INCLUDEssDEnoversion] of the new computer name. This procedure must be executed on all instances of the [!INCLUDEssDE] hosted on the computer. The instance name of the [!INCLUDEssDE] cannot be changed. To change the instance name of a named instance, install a new instance with the desired name, detach the database files from old instance, attach the databases to the new instance and drop the old instance. Alternatively, you can create a client alias name on the client computer, redirecting the connection to different server and instance name or server:port combination without changing the name of the instance on the server computer.
| Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version). |
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 [!INCLUDEmsCoName] Windows computer names, although spaces are not allowed. server is sysname, with no default.
When multiple instances of [!INCLUDEssNoVersion] 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.
[!INCLUDEssNoVersion] Setup sets this variable to the computer name during installation. By default, the computer name is the way users connect to an instance of [!INCLUDEssNoVersion] without requiring additional configuration.
The local definition takes effect only after the [!INCLUDEssDE] is restarted. Only one local server can be defined in each instance of the [!INCLUDEssDE].
[ @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 [!INCLUDEssDE] entry for the name of the computer hosting [!INCLUDEssNoVersion] to ACCOUNTS.
sp_addserver 'ACCOUNTS', 'local';
Rename a Computer that Hosts a Stand-Alone Instance of SQL Server
sp_addlinkedserver (Transact-SQL)
sp_dropserver (Transact-SQL)
sp_helpserver (Transact-SQL)
System Stored Procedures (Transact-SQL)
Security Stored Procedures (Transact-SQL)