sp_dropserver and sp_addserver not working

Question

I am using SQL Server Express 2008 R2 and I wanted to change the instance name from "machine name"\SQLEXPRESS2008R2 to just "machine name". I ran:

sp_dropserver 'old_name'

go

sp_addserver 'new_name', 'local'

go

Then restarted the SQL Service. Now when I look at

Select @@SERVERNAME --this is correct

But this isn't correct?

Select serverproperty('ServerName') --This still shows old name

So when I try to connect to my instance via SSMS I still have to connect using the old instance name isntead of the new on I just applied? What am I doing wrong? Why is the new name not taking?

Thanks,

S

Answer 1

This is from books online:

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.

And the first comment is correct. You'd have to reinstall SQL in order to change it to a default instance.

per BOL if you change the machine name with a named instances you have to use as follows:

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO