August 21, 2008 at 6:03 am
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.
August 21, 2008 at 6:50 am
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.
August 21, 2008 at 6:51 am
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
August 21, 2008 at 7:15 am
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
namenetwork_name
----------------------------
SQL2K5SWPRODSQL2K5SWPROD
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.
August 21, 2008 at 7:24 am
AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME
Wilfred
The best things in life are the simple things
August 21, 2008 at 8:50 am
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" 😉
August 27, 2009 at 9:24 am
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?
August 27, 2009 at 9:46 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply