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

  • 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.

  • 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.

  • 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

  • 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.

  • AFAIK, you need to restart SQL in order to make these changes for @@SERVERNAME

    Wilfred
    The best things in life are the simple things

  • 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" 😉

  • 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

  • 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply