SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


@@servername and serverproperty('servername') gives different values


@@servername and serverproperty('servername') gives different values

Author
Message
Mohammed Imran Ali
Mohammed Imran Ali
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 258
Hi,

While doing a regular maintenance work, I bumped into the following problem.

The following commands shows different values for the same server.

select serverproperty('servername')

Result => SQL2K5SWPROD

select @@servername

Result => SQL2K5SWSRV

The hostname command gives the result as 'SQL2K5SWPROD'. This is the correct name.

The 'OriginalMachineName' value in the registry location 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Machines' shows SQL2K5SWSRV. This is the incorrect value.

I am not sure if these are related to each other. Also, if the value is changed, will there be any impact on the server. I don't want to meddle with this production server.

Thanks in advance for your suggestion and direction.

Cheers,
Imran.
Khwaja Arshaduddin
Khwaja Arshaduddin
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 100
It looks like this is Instance name.Did you reinstalled the server?
The ServerName property provides the Windows NT server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.
ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed.
Wilfred van Dijk
Wilfred van Dijk
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 Visits: 1359
Found this answer in BOL:


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.


Wilfred
The best things in life are the simple things
Mohammed Imran Ali
Mohammed Imran Ali
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 258
The networkname was changed to 'SQL2K5SWPROD'. But SQL Server was installed after the name change happened. So I reckon this should show the new name for @@SERVERNAME as well.

I tried

sp_dropserver 'SQL2K5SWSRV', null

Command(s) completed successfully.

sp_helpserver

Msg 15205, Level 16, State 1, Procedure sp_helpserver, Line 17
There are no servers defined.

sp_addserver 'SQL2K5SWPROD','LOCAL'

Command(s) completed successfully.

sp_helpserver

name network_name
-------------- --------------
SQL2K5SWPROD SQL2K5SWPROD


After executing the commands the @@SERVERNAME should be returning 'SQL2K5SWPROD', but the it still returns the incorrect value ['SQL2K5SWSRV']

Any suggestion to get this corrected without a downtime would would be of much help.

Cheers,
Imran.
Wilfred van Dijk
Wilfred van Dijk
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 Visits: 1359
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME

Wilfred
The best things in life are the simple things
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20502 Visits: 17244
Wilfred van Dijk (8/21/2008)
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME


yes that is correct

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Ronzo
Ronzo
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 799
So, as a follow up, what about a more complex situation:

Clustered default instance of SQL Server with different virtual IP address and network name then the virtual name and address of the clustered Windows server it is installed on. (note: virtual as in cluster naming, not as in virtual server stuff).
The @@ServerName reports the SQL cluster name, while serverproperty('servername') reports the Windows cluster name. I do not want to change @@Servername to match serverproperty, I want to make serverproperty match @@Servername. How is this done in a clustered situation like this?


Have Fun!

Ronzo

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5940 Visits: 11771
Ronzo (8/27/2009)
So, as a follow up, what about a more complex situation:

Clustered default instance of SQL Server with different virtual IP address and network name then the virtual name and address of the clustered Windows server it is installed on. (note: virtual as in cluster naming, not as in virtual server stuff).
The @@ServerName reports the SQL cluster name, while serverproperty('servername') reports the Windows cluster name. I do not want to change @@Servername to match serverproperty, I want to make serverproperty match @@Servername. How is this done in a clustered situation like this?


How to: Rename a SQL Server Failover Cluster Instance
http://msdn.microsoft.com/en-us/library/ms178083.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search