Thursday, December 23, 2010

SQL server has been renamed but the operation is incomplete

I recently moved a number of SQL servers to a new domain and renamed the servers.  In the past this has worked fine with SQL 2008, however I was having trouble with an application on SQL 2005.

According to this blog post "The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed"

To find out what SQL thinks its called run sp_helpserver


To update the server name values use:
 
sp_dropserver 'ORIGNAME' -- where ORIGNAME is the name returned by sp_helpserver


sp_addserver 'CURRENTNAME', 'LOCAL' -- where CURRENTNAME is the current computer name