SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Update @@SERVERNAME to Actual Machine Name?

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 sysservers, from the record with the srvid column value of 0.
You can find it using the following query:

SELECT srvname FROM sysservers WHERE srvid = 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 sysservers would not automatically reflect the change.
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.

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:

DECLARE @MachineName NVARCHAR(MAX)
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 = srvname FROM sysservers WHERE srvid = 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 srvname FROM sysservers WHERE srvid = 0 AND srvname <> @@SERVERNAME)
	PRINT 'Your server name was changed. Please restart the SQL Server service to apply changes.';

What the script does:

  1. It uses SERVERPROPERTY system functions to get the actual machine name and instance name of SQL Server.
  2. It compares the machine name to the value in sysservers.
  3. 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 sysservers, 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 sysservers will be different.

Special Notes:

  • 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.

 

The post How to Update @@SERVERNAME to Actual Machine Name? appeared first on Madeira Data Solutions.

Eitan Blumin

Eitan Blumin is a SQL Server database expert and a senior consultant at Madeira SQL Server Services. He has more than 10 years of experience in all fields of the SQL Server DBA role, including but not limited to: Database design, management, development, tuning, replication, backup management, security management, SSIS, SSRS, encryption and more. Eitan also has 10 years of experience in ASP web development, and some experience in a wide variety of development environments such as PHP, C, C++, C#, VB, Java, Perl, Assembler and more.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...