Getting Wrong Server Name

Question

I am executing a query from which i want to get all server name installed on a system
So I did it with this query

select srvname  from sysservers    

It gives me server names but it is not the correct server name it gives me srvname

WIN-1BKHGVK7J3A\SQLSERVER2008R2

but this is not my server name also i tried to do it from another way by this query

Select @@Servername   

it also gives above wrong server name WIN-1BKHGVK7J3A\SQLSERVER2008R2

But when i did it with this query

SELECT SERVERPROPERTY ('ServerName')  

it gives correct server name which is AIS-OCTACORE\SQLserver2008r2
but limitation with this is it only gives current server name and i want to get all server name installed on a system
can you please tell why i'm getting wrong servername??
Also how could i get all server names

Answer 1

To check what the current values are set to

SELECT ServerProperty('machinename') as [machinename]
    ,ServerProperty('ServerName') as [ServerName]
    ,@@ServerName as [@@ServerName];

To correct the issue run the following:

EXEC sp_dropserver 'old_server_name';
GO

EXEC sp_addserver 'new_server_name', 'local';
GO

You will need to restart the SQLSERVER service

Answer 2

This can happen when the server name is changed after SQL Server was installed.

You could try sp_dropserver and sp_addserver to change it back: http://www.brentozar.com/blitz/servername-not-set/

Im not sure i would though you dont know why it was changed in the first place it might break something.