Thursday, 28 May 2009

Renaming your SQL Server

If you are pondering changing the name of your development machine, you must remember to change the name of your SQL Server as well. I didn't know that and spent a good half an hour trying to run a simple query in the format


Select <ColumnNames> from [ServerName].[DatabaseName].dbo.[TableName]



The server came back with the following error

Could not find server 'GGTFS2-0BUILD2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.


Struck by the need to create a linked server for itself, I executed the following to find out what servers are present


Select * from sys.sysservers



Only to find that the table still contains the previous name of my machine. The resolution is pretty straight forward. Execute the following

sp_dropserver '<OLD SERVER NAME>'

go



sp_addserver '<NEW SERVER NAME>', 'local'

go


Restart your SQL Server and bang it starts working again :)

No comments: