Server rename

Question

Server rename

kd11

Hall of Fame

Points: 3447
More actions
March 20, 2015 at 9:34 am

#315856

I have a 2012 machine (VM) which I had rename from "AAAA" to "BBBB". I went into SQL and sp_drop "AAAA" and sp_addserver "BBBB", restarted the services then went back into SMS and type:
select @@servername and the older servename keeps appearing. However when I look into Server Manager it has the correct new name "BBBB". What could be the reason why the old name keeps coming up when doing select @@servername and I get "BBBB" already exists when doing sp_addserver. The older servername was on a different box which had a different IP-Address.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the running values for @@servername stay in place, until the service is stopped and started.

if you select * from sys.servers where server_id = 0, that is the new name, of course, but it would not change @@servername until the service gets bounced.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kd11 Hall of Fame Points: 3447 More actions · Answer 2

The Server was restarted, but yet the new name is not being accepted.

Michael L John One Orange Chip Points: 27056 More actions · Answer 3

sp_addserver @server = 'Servername', @local = 'local'.

This re-sets the value of @@SERVERNAME

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

richard.bowles SSCommitted Points: 1619 More actions · Answer 4

This link https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx indicates that if you use

select SERVERPROPERTY('SERVERNAME')

you should see the new name. In the remarks section on the 'servername' property you will find a statement that @@servername will not return the new value once changed (but there is conflicting information a few lines down that seem to say @@servername will return the new name if sp_addserver and sp_dropserver are used to change the name).

Chris.Cronrath Mr or Mrs. 500 Points: 518 More actions · Answer 5

Make sure after the sp_addserver you restart SQL. Also, you might want to do a sp_dropserver to remove the old name.

Lowell SSC Guru Points: 323518 More actions · Answer 6

Michael L John (3/20/2015)
sp_addserver @server = 'Servername', @local = 'local'.
This re-sets the value of @@SERVERNAME

....combined with a server stop and start, that is correct...but not as a stand alone command.

the value for @@servername is read at configuration startup.

it sets the value in sys.servers where server_id = 0 immediately, but doesnot affect @@servername,

EXEC sp_dropserver 'GDC-SQL-P0X'

EXEC sp_addserver 'GDC-SQL-P09', 'local'

select * from sys.servers where server_id = 0

select @@servername

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kd11 Hall of Fame Points: 3447 More actions · Answer 7

I didn't everything what was mention in this thread at the being, since "Select @@servername" is returning Null But "select SERVERPROPERTY('SERVERNAME')" is returning the new servername then I'm not going to worry about it. However inside Report Services Configuration Manager under databases it still show the SQL Server Name as the "OLD Server Name " and not the "New Server Nam"e So I guress I have to blow away my Report Configuration and create a new one.