About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Sunday, February 10, 2013

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
      

No comments:

Post a Comment