I just stumbled across an issue in my SQL Server 2008 R2 - When I call @@SERVERNAME, it's returning my OLD computer's machine name, rather than the current one. Why is this? And how can I fix it? SQL Server somehow is remembering the old machine name.
asked Jan 7, 2013 at 15:03
6 Answers
This is well known and documented, see Rename a Computer that Hosts a Stand-Alone Instance of SQL Server:
When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use the following steps to update system metadata that is stored in sys.servers and reported by the system function @@SERVERNAME:
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO
You can also use SERVERPROPERTY('MachineName') which is guaranteed to always return the updated name:
MachineName Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') will return the current active node in a cluster, or the same value as 'MachineName' on a non-clustered instance.
Edit (by a 3rd party) to add WEFX's comment in case anyone misses it:
Also, you'll need to restart your SQL services (or reboot the SQL Server) in order for
SELECT @@SERVERNAMEto return the accurate (new) servername
answered Jan 7, 2013 at 15:08
-
4Also, you'll need to restart your SQL services (or reboot the SQL Server) in order for 'SELECT @@SERVERNAME' to return the accurate (new) servername.– WEFXCommented Oct 16, 2014 at 18:22
-
declare @s varchar(50); select @s = cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar); exec sp_dropserver @@servername; exec sp_addserver @s,local;– JeremyCommented Sep 23, 2017 at 3:32
I have tried all the possible solutions and the approved answer didn't work for me. I have surfed a bit and come up with the perfect solution. I hope somebody find this helpful.
1) Open registry by Window + R key. Type regedit
2) Go to *HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server* You will see plenty of numbered directories ( 100,120,130 ....)
OR
you can simply type "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" in the address bar of Registry Editor
3) go through all the numbered directories and see if you can find "Machines" directory inside
4) Once you find "Machines" change the OriginalMachineName key to the server name you want it to be. That's actually Original machine name when the windows was first installed.
PS: My path was OriginalMachineName > Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\Machines
-
Interestingly, for AWS SQL Server AMIs the value at this registry location is different than the default machine name when you spin up a new machine, which is then likely different if you manually name the machine. The only solution that works consistently across our machines is using SERVERPROPERTY('MachineName') instead of @@SERVERNAME. Commented Aug 17, 2018 at 22:34
I suspect this is because the instance was a default installation and inherited the machine name at the time and has kept it. Try this?
SELECT SERVERPROPERTY('MachineName')
Sometimes you can get an error There are still remote logins or linked logins for the server 'yourServerName' when you run sp_dropserver 'oldServerName';
If you have this kind of error then try to run sp_dropserver 'oldServerName', 'droplogins'; instead.
First, with two SQL queries below, check your Windows device name and your MSSQL server name respectively:
SELECT HOST_NAME() -- Get Windows device name
SELECT @@SERVERNAME -- Get MSSQL server name
Now, you could check your Windows device name "DESKTOP-NEW" and your MSSQL server name "DESKTOP-OLD\SQLEXPRESS" respectively as shown below:
Next, with the SQL queries below, you can drop your old MSSQL server "DESKTOP-OLD\SQLEXPRESS" and add the new MSSQL server "DESKTOP-NEW\SQLEXPRESS":
EXEC sp_dropserver 'DESKTOP-OLD\SQLEXPRESS' -- Drop server
GO
EXEC sp_addserver 'DESKTOP-NEW\SQLEXPRESS', local -- Add server
GO
Now, you could drop the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" and add the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" as shown below:
But, even if you check your MSSQL server name again, your MSSQL server name is still the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" as shown below. *But no worries, in spite of that, the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" is actually dropped properly and the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" is actually added properly:
If you really want to check that the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" is actually added properly, restart MSSQL with SQL Server Configuration Manager(SSCM). This is How to find SQL Server Configuration Manager in your Windows Machine:
Then, if you check your MSSQL server name again, your MSSQL server name is now the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" as shown below.
-
Note that HOST_NAME() returns the machine name where the SSMS instance is executing, not the Server host of the SQL Server you're connected to. If you are connected to a remote SQL Server instance via SSMS, HOST_NAME() will give you YOUR machine name, not the name of the SQL Server instance. You should use SERVERROPERTY('MachineName') instead. Commented Jun 25, 2023 at 18:01
In my case I couldn't drop the old name; I could restart MSSQL or the VM all I wanted, @@SERVERNAME kept returning the old name while every other method (including SERVERPROPERTY('SERVERNAME')) was returning the new name, which was causing all sorts of issues. Turns out dropping the new name and re-adding it (and restarting MSSQL of course) fixes this odd issue:
sp_dropserver <new_name>; GO
sp_addserver <new_name>, local; GO
SELECT SERVERPROPERTY('ServerName');