venerdì 27 aprile 2012 20:20
OS - Windows Server 2003 SP3 64-bit Standard
SQL Server - 2005 SP2 64-bit Standard
select @@servername returns value ServerA
However, select server_id, name from sys.servers returns:
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.
- Modificato Jeelani Kamal venerdì 27 aprile 2012 20:21
Tutte le risposte
venerdì 27 aprile 2012 20:26
>>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,
venerdì 27 aprile 2012 20:45
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.
venerdì 27 aprile 2012 20:55
Are you running this on a SQL server which is part of a cluster?
you can confirm it by running
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.
venerdì 27 aprile 2012 21:16
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.
venerdì 27 aprile 2012 21:23
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
- Contrassegnato come risposta Jeelani Kamal lunedì 30 aprile 2012 14:46