sp_addserver (T-SQL)

Defines a remote server or the name of the local Microsoft® SQL Server™. sp_addserver is provided for backward compatibility. Use sp_addlinkedserver.

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 NT® computer names, although spaces are not allowed. server is sysname, with no default.

[@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 server. (The Setup program sets this variable to the computer name during installation. It is recommended that the name not be changed. By default, the computer name is the way users connect to 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 or not 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, then no error is raised. @local must be specified if named parameters are not used.

Return Code Values

0 (success) or 1 (failure)

Remarks

To execute a stored procedure on a remote server (remote procedure calls) running an earlier version of SQL Server, add the remote server using sp_addserver. To execute a stored procedure (or any distributed query) on a remote server running SQL Server version 7.0, 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

Only members of the setupadmin and sysadmin fixed server roles can execute sp_addserver.

Examples

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

sp_addserver 'ACCOUNTS'

See Also