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
PRINT ‘Server name differs’
IF @NEWNAME IS NOT NULL
IF @OLDNAME IS NOT NULL
PRINT ‘Dropping server ‘ + @OLDNAME
EXEC sp_dropserver @OLDNAME
PRINT ‘Dropped server ‘ + @OLDNAME
PRINT ‘Adding server ‘ + @NEWNAME
EXEC sp_addserver @NEWNAME, ‘local’
PRINT ‘Added server ‘ + @NEWNAME
PRINT ‘Now stop and restart SQL Server’