I was in the process of installing SCOM2012 and came to the part where I had to specify the SQL server name. I quickly logged onto the SQL server and ran SELECT @@servername to double check the SQL server name. Surprisingly, the server name was wrong. The OS server name was sqlcloud, but the SQL server name was testserver.
The SQL server name will usually be the name of the host/physical server. In my case the name was showing something different. I tried to add my server name using the query: sp_addserver ‘sqlcould’, ‘local’; but got the error:
Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74
The server ‘sqlcould’ already exists
For some reason the right name was stuck up somewhere, and not allowing me to change to the correct name. What to do in this scenario is to drop the server name (sqlcould and testserver) and then add back only sqlcloud.
To change the server name, do the queries below:
sp_dropserver ‘testserver’; sp_dropserver ‘sqlcould’ — remove the conflicting names (don’t drop any linked servers)
sp_addserver ‘sqlcould’, ‘local’ — add in the correct name
Take extra care not to go ahead and drop all the server names you find. Some of them might be linked servers that are required by queries/functions.
After restarting the SQL server I was able to see the correct server name.