FixServerName

Many like to use the global variable @@ServerName when you need to provide the instance name of your SQL Server.  However, if the machine has been renamed after SQL Server was installed, this variable will be incorrect – it will have the previous instance name of SQL Server.

ServerProperty('ServerName')) will always return the correct instance name.  But it is a bit clumsy to use because its datatype is sql_variant, so you have to first convert it to char or varchar.

This proc will identify and fix the problem.  A service stop/start is required after running the fix.

Use Admin

go

IF Object_ID('dbo.FixServerName') Is Not Null

      DROP PROC dbo.FixServerName

go

CREATE PROC dbo.FixServerName

      @Debug bit = 1

AS

/*    DATE        AUTHOR            REMARKS

      2/10/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Determines if @@ServerName is the same as ServerProperty('ServerName').

                  If not, then the server has likely been renamed after SQL Server

                  was installed. 

                 

            To fix, run this with @Debug = 0, then stop/start the SQL Server service.

     

      USAGE

            FixServerName           -- Examine only, don't fix

            FixServerName 0         -- Fix

     

*/

DECLARE @ServerNameGlobalVar varchar(50),

            @ServerNameProperty varchar(50),

            @Msg varchar(1000)

SET @ServerNameGlobalVar = @@ServerName + 'h'

SET @ServerNameProperty = Convert(varchar(50), ServerProperty('ServerName'))

IF @ServerNameGlobalVar = @ServerNameProperty

      BEGIN

            SET @Msg = '@@ServerName has the correct value.  You can use @@ServerName whenever you need '

                        + Char(13) + 'to provide the SQL instance name.  Nothing to fix.'

                        + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                        + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

            Print @Msg

      END

     

ELSE

      BEGIN

            IF @Debug = 1

                  BEGIN

                        SET @Msg = '@@ServerName does NOT have the correct value. Needs to be fixed.'

                                    + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                                    + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

                                    + Char(13) + 'To fix, run the following, then stop/start the SQL Server service.'

                                    + Char(13) + '    Exec FixServerName 0'

                        Print @Msg

                  END                          

                 

            ELSE             

                  BEGIN

                        SET @Msg = 'Exec master.dbo.sp_dropserver ''' + @@ServerName + ''''

                        Print @Msg

                        Exec(@Msg)

                        SET @Msg = 'Exec master.dbo.sp_addserver ''' + @ServerNameProperty + ''', ''local'''

                        Print @Msg

                        Exec(@Msg)

                       

                        SET @Msg = 'Need to stop/start the SQL Server service, or reboot, for change to take effect.'

                        Print ''

                        Print @Msg

                  END

                 

      END