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 :)