Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

@@servername and serverproperty('servername') gives different values Expand / Collapse
Author
Message
Posted Thursday, August 21, 2008 6:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:09 AM
Points: 72, Visits: 251
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.

Post #556439
Posted Thursday, August 21, 2008 6:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 3:57 AM
Points: 75, 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.
Post #556495
Posted Thursday, August 21, 2008 6:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:52 AM
Points: 930, Visits: 1,135
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
Post #556498
Posted Thursday, August 21, 2008 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:09 AM
Points: 72, Visits: 251
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.
Post #556530
Posted Thursday, August 21, 2008 7:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:52 AM
Points: 930, Visits: 1,135
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME

Wilfred
The best things in life are the simple things
Post #556547
Posted Thursday, August 21, 2008 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 6,365, Visits: 13,695
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"
Post #556641
Posted Thursday, August 27, 2009 9:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:31 PM
Points: 507, Visits: 600
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

Post #778407
Posted Thursday, August 27, 2009 9:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
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
Post #778424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse