Modify the SQL Server Name (@ servername)

Modify the SQL Server Name (@ servername)

 

 

After SQL server2005 is installed, the computer name (xiage) is changed ),

However, the @ servername returned by the database is still fb4b6c04012d449/sqlexpress (the original default computer name/sqlexpress is not automatically updated .)

We can modify the settings by deleting and adding services.

 

Before Modification

Select @ servername

Result

-- Fb4b6c04012d449/sqlexpress

After modification

Select @ servername

Result

-- Xiage/sqlexpress

 

Call the system stored procedure modification Service

 

Delete a service

Sp_dropserver 'fb4b6c04012d449/sqlexpress ', null

Add a new service

Sp_addserver 'xiage/sqlexpress ', 'local', null

 

 

Excerpted from: SQL Server books online

1. sp_dropserver

Delete a server from the list of known remote servers and linked servers in the local SQL server instance.

Syntax sp_dropserver [@ Server =] 'server' [, [@ droplogins =] {'droplogins' | null}]

 

You must delete the login name before connecting to the server. To delete all remote servers and linked server login names of the server when deleting the server, use the droplogins parameter.

 

Parameters

[@ Server =] 'server'

The server to be deleted. The data type of the server is sysname, and no default value is set. Server must exist.

[@ Droplogins =] 'droplogins' | null

Indicates that if droplogins is specified, the related remote server and linked server login name must be deleted for the server. @ Droplogins the data type is Char (10

). The default value is null.

 

Return code value 0 (successful) or 1 (failed)

 

You must have the alter any linked server permission on the server.

2. sp_addserver

Defines the name of the remote server or local SQL server instance.

Syntax sp_addserver [@ Server =] 'server' [, [@ local =] 'local'] [, [@ duplicate_ OK =] 'duplicate _ OK ']

 

Remarks

 

To execute a stored procedure (Remote Procedure Call) on a remote server running an earlier version of SQL Server, use sp_addserver to add a remote server. To run

To execute stored procedures or any distributed queries on remote servers of SQL Server 7.0 or later, use sp_addmediaserver to add servers.

To set or clear server options, use sp_serveroption.

Sp_addserver cannot be used in a user-defined transaction.

Parameters

[@ Server =] 'server'

The name of the server. The server name must be unique and comply with the Microsoft Windows computer name rules, but cannot contain spaces. The data type of the server is sysname.

, No default value.

 

If multiple SQL Server instances are installed on the computer, the instance runs on an independent server. Use the following format to reference the server to specify the Instance name:

Servername/InstanceName.

 

[@ Local =] 'local'

Specify whether the server to be added is a local server or a remote server. @ Local: the data type is varchar (10). The default value is null. If you specify @ local as local

@ Server is defined as the name of the local server, and the @ servername function returns the value of the server.

 

The SQL Server installer sets this variable to the computer name during installation. We recommend that you do not change the name. By default, you can connect to the SQL

Server instance without additional configuration.

 

The local definition takes effect only after the server is shut down and restarted. Each server can only define one local server.

 

[@ Duplicate_ OK =] 'duplicate _ OK'

Specifies whether duplicate server names are allowed. @ Duplicate_ OK: the data type is varchar (13), and the default value is null. @ Duplicate_ OK only duplicate_ OK or null

These two values. If duplicate_ OK is specified and the server name to be added already exists, no error will occur. If the name parameter is not used, @ local must be specified.

 

Return code value: 0 (successful) or 1 (failed)

Permission: requires a member with the setupadmin fixed server role.