the effects of changing a machine's name on sql server

i decided to post about this because, i shit you not, every single member of our solution delivery department (aka our consultants) and a good number of our developers have approached me with issues after renaming their machine. one may be wondering why so many people are renaming their computer in the first place. simple - in my neck of the woods, we have several baseline virtual machine images built up for anyone to use. when someone begins using it, they typically give the 'computer' a new name (especially if they want to join it to the domain). the problem is that sql server doesn't pick up on this and programs that try to connect to the server by referencing it by the new name (or new name\instance name) will get errors along the lines of 'the server could not be found in the sysservers list. try adding the server using sp_addlinkedserver'. don't do that; it won't work. try running this command against the master database instead:

SELECT @@servername

it will probably return the name of the machine (\instance name) before you renamed it. the fix is this:

sp_dropserver '{oldname}'

where {oldname} is whatever was returned by your first query. after that, run this:

sp_addserver '{newname}(\instancename)', 'LOCAL'

obviously only include (\instancename) if it is a named instance of sql server. restart the sql server service, then re-run your original query and it should return the correct name (you will have to either open a new query editor or re-connect the query editor screen you originally used as restarting the service will cause a disconnect).