CODEWITHSUNDEEP
sqlsql-server-2008-r2
Amit Bisht
Amit Bisht

Reputation: 5136

Getting Wrong Server Name

enter image description hereI 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

Upvotes: 10

Views: 13054

Answers (3)

Andrej Hribernik
Andrej Hribernik

Reputation: 326

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

Upvotes: 20

Linda Lawton - DaImTo
Linda Lawton - DaImTo

Reputation: 116869

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.

Upvotes: 3

Nagaraj S
Nagaraj S

Reputation: 13474

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

  1. To findout "SERVER NAME"

    SELECT SERVERPROPERTY('MACHINENAME')

  2. To findout "SERVER NAME" with "INSTANCE NAME" (If its Named Instance)

    SELECT SERVERPROPERTY('SERVERNAME')

  3. To findout "CLIENT MACHINE NAME"(Local Machine Name)

    SELECT HOST_NAME()

Upvotes: 2

Related Questions