Written By: Eitan Blumin 07/12/2017
The system global variable @@SERVERNAME can be used to return the name of the machine the SQL Server is hosted on.
This variable is derived from the system table sys.servers, from the record with the server_id column value of 0.
You can find it using the following query:
SELECT name FROM sys.servers WHERE server_id = 0
However, this value is automatically configured only during the initial installation of SQL Server.
If, for whatever reason, the Windows Computer Name is changed after SQL Server is already installed, then @@SERVERNAME and the information in sys.servers would not automatically reflect the change (sysservers in older SQL versions).
This means that @@SERVERNAME contains the incorrect value for the machine name.
Sometimes, and especially in production environments, the value in that global variable is important and is used as part of business processes.
And if @@SERVERNAME doesn’t reflect the actual server name, it could cause problems.
Alternatively, it’s possible (and maybe even best) to use the SERVERPROPERTY function instead to get the actual server name, or machine name, or instance name. The information available through this function should be up-to-date even after you rename the Windows Computer Name.
Here is a quote from the official Microsoft Documentation:
Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer.
In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.
Furthermore, the @@SERVERNAME variable is what’s used by SQL Server Replication. So, if it has an incorrect value, you may encounter a situation where replication doesn’t work because it doesn’t recognize the correct server name.
Either way, it’s best to have SQL Server save the correct meta-data of your machine.
In such cases, it’s best to update the value of the global variable to match the actual Windows Computer Name, and we will need to do that manually (using the sp_addserver and sp_dropserver stored procedures as noted by Microsoft).
DECLARE @MachineName NVARCHAR(60)
SET @MachineName = CONVERT(nvarchar,SERVERPROPERTY('ServerName'));
IF @MachineName IS NULL
PRINT 'Could not retrieve machine name using SERVERPROPERTY!';
DECLARE @CurrSrv VARCHAR(MAX)
SELECT @CurrSrv = name FROM sys.servers WHERE server_id = 0;
IF @CurrSrv = @MachineName
PRINT 'Server name already matches actual machine name.'
PRINT 'Dropping local server name ' + @CurrSrv
EXEC sp_dropserver @CurrSrv
PRINT 'Creating local server name ' + @MachineName
EXEC sp_addserver @MachineName, local
IF EXISTS (SELECT name FROM sys.servers WHERE server_id = 0 AND name <> @@SERVERNAME)
PRINT 'Your server name was changed. Please restart the SQL Server service to apply changes.';
It’s important to note that the @@SERVERNAME global variable will NOT reflect the change until the SQL Server service is restarted.
If you don’t restart the server, then the contents of @@SERVERNAME and sys.servers will be different.