The @@SERVERNAME Global Variable
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:
Transact-SQL
SELECT name FROM sys.servers WHERE server_id = 0
1
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).
For this purpose, I’ve created the script below:
Transact-SQL
DECLARE @MachineName NVARCHAR(60) SET @MachineName = CONVERT(nvarchar,SERVERPROPERTY('ServerName')); IF @MachineName IS NULL BEGIN PRINT 'Could not retrieve machine name using SERVERPROPERTY!'; GOTO Quit; END DECLARE @CurrSrv VARCHAR(MAX) SELECT @CurrSrv = name FROM sys.servers WHERE server_id = 0; IF @CurrSrv = @MachineName BEGIN PRINT 'Server name already matches actual machine name.' GOTO Quit; END PRINT 'Dropping local server name ' + @CurrSrv EXEC sp_dropserver @CurrSrv PRINT 'Creating local server name ' + @MachineName EXEC sp_addserver @MachineName, local Quit: 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.';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DECLARE @MachineName NVARCHAR(60)
SET @MachineName = CONVERT(nvarchar,SERVERPROPERTY('ServerName'));
IF @MachineName IS NULL
BEGIN
PRINT 'Could not retrieve machine name using SERVERPROPERTY!';
GOTO Quit;
END
DECLARE @CurrSrv VARCHAR(MAX)
SELECT @CurrSrv = name FROM sys.servers WHERE server_id = 0;
IF @CurrSrv = @MachineName
BEGIN
PRINT 'Server name already matches actual machine name.'
GOTO Quit;
END
PRINT 'Dropping local server name ' + @CurrSrv
EXEC sp_dropserver @CurrSrv
PRINT 'Creating local server name ' + @MachineName
EXEC sp_addserver @MachineName, local
Quit:
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.';
What the script does:
It uses SERVERPROPERTY system functions to get the actual machine name and instance name of SQL Server.
It compares the machine name to the value in sys.servers.
If the value is different, then the script will use the sp_dropserver and sp_addserver system stored procedures to drop the incorrect server name from sys.servers, and then add the correct machine name.
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.
Special Notes:
The script can be used as-is. No need to change anything in it. Just copy-paste and run on any server.
The contents of this post are relevant to SQL versions starting from SQL Server 2008.
The script should work for named instances as well as default instances.
The script requires the ALTER ANY LINKED SERVER permission on the server.
The script will not make any changes if it’s unnecessary, therefore it can be executed multiple times without worry.
Special thanks to Nathan Lifshes for letting me know that Replication uses @@SERVERNAME !
댓글