SQL Server Oddity and a fix.

Recently I found that @@SERVERNAME does not actually return the name of the server that the machine is running.

It returns the name that was last set using sp_addserver ‘NAME’, local

This makes a difference if you are  creating an image of a machine as a master copy.
All of the copied machines will think that they are the master machine.

The following script should detect and repair incorrect server names:

SET NOCOUNT ON

DECLARE @OLDNAME VARCHAR(200)
DECLARE @NEWNAME VARCHAR(200)

SELECT @OLDNAME = @@SERVERNAME
SELECT @NEWNAME = CAST( SERVERPROPERTY(‘ServerName’) AS VARCHAR(200))

select @OLDNAME, @NEWNAME

IF @OLDNAME <> @NEWNAME
BEGIN
    PRINT ‘Server name differs’
    IF @NEWNAME IS NOT NULL
    BEGIN
        IF @OLDNAME IS NOT NULL
        BEGIN
            PRINT ‘Dropping server ‘ +  @OLDNAME          
            EXEC sp_dropserver @OLDNAME
            PRINT ‘Dropped server ‘ +  @OLDNAME          
        END
        PRINT ‘Adding server ‘ + @NEWNAME
        EXEC sp_addserver @NEWNAME, ‘local’
        PRINT ‘Added server ‘ + @NEWNAME
        PRINT ‘Now stop and restart SQL Server’
    END
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s