Getting the wrong server name

Question

I execute a request from which I want to get all the server name installed on the system. So I did it with this query

select srvname from sysservers 

It gives me server names, but this is the wrong server name that gives me srvname

WIN-1BKHGVK7J3A\SQLSERVER2008R2

but this is not my server name and I tried to do it differently with this request

 Select @@Servername 

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

But when I did this with this request

 SELECT SERVERPROPERTY ('ServerName') 

It gives the correct server name, which is AIS-OCTACORE\SQLserver2008r2
but the limitation with this is only the name of the current server, and I want the whole server name to be installed on the system
Could you tell me why I get the wrong server name?
Also how can I get all server names

Answer 1

To check if current values ​​are set to

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

To fix the problem, follow these steps:

 EXEC sp_dropserver 'old_server_name'; GO EXEC sp_addserver 'new_server_name', 'local'; GO 

You need to restart the SQLSERVER service

Answer 2

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

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

I’m not sure that although you don’t know why this was changed, it could break something in the first place.

Answer 3

Although the @@ SERVERNAME function and the SERVERNAME property of the SERVERPROPERTY function can return strings with the same format, the information may be different. The SERVERNAME property automatically reports changes to the network name of the computer.

In contrast, @@ SERVERNAME does not report these changes. @@ SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure. technet p>

• To find out "SERVER NAME"

  SELECT SERVERPROPERTY ('MACHINENAME')

• To find "SERVER NAME" with "TOOL NAME" (if its a Named instance)

  SELECT SERVERPROPERTY ('SERVERNAME')

• To find out "CUSTOMER'S MACHINE NAME" (local machine name)

  SELECT HOST_NAME ()