Local SQL Installation think it's somewhere else

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Local SQL Installation think it's somewhere else

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-02-10 : 11:50:17

Hi,

I have a local installation of SQL Server 2008 which was put on my machine by an operations guy before I started work. Generally it seems very happy but on rare occasions it seems to think it's operating on a machine other than mine.

Assuming my machine name is MY_MACHINE the installation is called SQLEXPRESS and calling MY_MACHINE\SQLEXPRESS is a trouble-free way of finding an connecting to the SQL Server instance.

I've found trouble with two particular operations so far though. Firstly when you set up Log Shipping either using the wizard or by calling the inbuilt stored procedures for the process, the backup job step is targeted not to backup MY_MACHINE\SQLEXPRESS but OTHER_MACHINE\SQLEXPRESS. The OTHER_MACHINE in this instance actually belongs to a manager and doesn't even have a SQL Server instance so - obviously - the job fails. But why on earth it's inserted as a target in the first place I don't know.

That's fixed easily by manually altering the job. The other peculiarity is trying to turn on replication which gives me the message:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'OTHER_MACHINE\SQLEXPRESS'.

And I can't seem to get past that.

So can anyone tell me why my instance seems to think it's actually running on a different machine, and how I can fix it so that it believes itself to be running where it actually is?

Cheers,
Matt

mattt
Posting Yak Master

194 Posts

Posted - 2011-02-10 : 12:20:18

Seems @@SERVERNAME was set wrong. In case anyone else finds this on google or whatnot, it can be fixed thusly:


DECLARE @InternalInstanceName sysname;
DECLARE @MachineInstanceName sysname;

SELECT @InternalInstanceName = @@SERVERNAME, 
 	@MachineInstanceName = CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) 
				+ COALESCE('\' + CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '');

IF @InternalInstanceName <> @MachineInstanceName
BEGIN

	-- Rename the instance
	EXEC sp_dropserver @InternalInstanceName;
	EXEC sp_addserver @MachineInstanceName, 'LOCAL';
END

-- You now need to restart the server for the changes to take effect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources