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