sys.servers and @@servername

Question

OS - Windows Server 2003 SP3 64-bit Standard

SQL Server - 2005 SP2 64-bit Standard

Fact 1:

select @@servername returns value ServerA

 

Fact 2:

However, select server_id, name from sys.servers returns:

server_id, name

0              ServerA

1              ServerB

2              ServerC

Using SQL Server Management Studio:

I cannot connect to ServerA.

However, using SQL Server Management Studio, I could connect to the actual windows name of the Server, ServerWin.

Any help to unravel this puzzle is appreciated.

+ive

 

Answer 1

>>select @@servername returns value ServerA

It is returning ServerName of the current SQL Server you are connected to in your case it is ServerA

while Sys.Servers can return information related to linked SQL Server as well as your current SQL server. in your case  ServerB and ServerC are linked SQL server.

>>Using SQL Server Management Studio:

>>I cannot connect to ServerA.

>>However, using SQL Server Management Studio, I could connect to the actual windows name of the Server, ServerWin

One possibility, ServerA is a named instance and not default instance in that case you may want to use

ServerWin\ServerA as a server name in management studio

make sure SQL Server browser service is running else you need to specify port number.

Hope that helps,

Answer 2

Getting close. Thank you.

ServerB and ServerC are indeed linked servers.

ServerA is a named instance -TEST\ServerA. However, the OS Server Name is PROD. SQL Server Management Studio connects only to named instance PROD\ServerA and does not connect to TEST\ServerA. However, TEST\ServerA shows up as the result of select @@servername and in select name from sys.servers.

Thank you.

+ive

Answer 3

Are you running this on a SQL server which is part of a cluster?

you can confirm it by running

SELECT SERVERPROPERTY('Isclustered')

if it reutns 1 it is clustered and in that case TEST\ServerA Test is you failover clustername.

Let me know.

As always, open configuration manager (start-->Run-->SQLServerManager.msc) and check the instance name.

Answer 4

Thank you.

This SQL Server is not part of a cluster.

Configuration Manager only shows instance name, i.e, ServerA. It does not show the first part, i.e, PROD or TEST.

PROD\ServerA is the instance that SQL Server Management Studio connects to but TEST\ServerA is the instance that shows up in select @@servername or select name from sys.servers.

I am wondering if SQL Server only goes by Servername from the actual Windows server and adds instance name from Configuration Manager. TEST could have been the original name of the Server that was build which at some point in time could have got renamed to PROD. This change might not have been reflected into sys.servers. Just a thought.

+ive

Answer 5

NO 7, I believe computer might have been renamed after SQL Server was installed.

You can correct it by using as documented here

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