Alex Meyer-Gleaves

on sql server

Renaming a computer running SQL Server

You may have noticed that after renaming a computer running SQL Server that the value returned for @@SERVERNAME has not been updated. It returns the computer name as it was during installation of SQL Server. The SERVERPROPERTY function does take into consideration changes made to the computer name when the ServerName property is requested.

To fix the server name you need to run the sp_dropserver and sp_addserver stored procedures. Instead of typing in the computer and instance names you can use the current @@SERVERNAME value for the old name, and SERVERPROPERTY('ServerName') for the new. The TSQL below fixes the name of your local SQL Server and works for default and named instances. You will need to restart the service for the change to take affect.

EXEC sp_dropserver @@SERVERNAME
GO

DECLARE @server nvarchar(128)
SELECT @server = CAST(SERVERPROPERTY('ServerName') AS nvarchar(128))
EXEC sp_addserver @server, 'local'
GO

Here are some links if your keen further information.

The last link refers to a KB article for SQL Server 2000 but still contains relevant information.

profile
AUTHOR

Alex Meyer-Gleaves

I'm a Technical Architect living in Australia (that island like continent in the southern hemisphere). I love Microsoft .NET and C#. I hate early mornings, slow drivers and Lotus Notes.