March 20, 2015 at 9:34 am
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.
March 20, 2015 at 10:37 am
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
March 20, 2015 at 11:51 am
The Server was restarted, but yet the new name is not being accepted.
March 20, 2015 at 12:07 pm
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/
March 20, 2015 at 12:09 pm
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).
March 20, 2015 at 12:13 pm
Make sure after the sp_addserver you restart SQL. Also, you might want to do a sp_dropserver to remove the old name.
March 20, 2015 at 12:19 pm
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
March 20, 2015 at 12:24 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply