Some time while firing @@servername
command on SQL server we tend to see that the name with which we have
connected to the SQL server varies with the actual output of the
@@servername command. This leaves many DBA in a state of confusion and
then they try to find the solution to resolve this ambiguity.
Generally this happens when we change the name of the windows machine on whom the SQL Server is running but forget to change the name in the SQL server. SQL server name does not reflect this change and then we need to manually add the new server name and remove the old server name.
Example :
We have changed the windows machine name from Ahsi-PC to Shashank but when we look for the SQL Server name it will give the old windows machine name. You can see this in the image below. The new name is listed in Object Exporer, but the old name appears on the right as the result of the query.
To correct this, we need to first run sp_dropserver ‘Old server Name’ in the master database.
Next, we run sp_addserver ‘New server name’,'local’ in the master database. After running this, restart the SQL Server services.
Now check the new name of the instance by running select @@servername.
Now the SQL instance name has been changed and matches the windows name change and the minimum rights required to perform this action are in the setupadmin server role.
Generally this happens when we change the name of the windows machine on whom the SQL Server is running but forget to change the name in the SQL server. SQL server name does not reflect this change and then we need to manually add the new server name and remove the old server name.
Example :
We have changed the windows machine name from Ahsi-PC to Shashank but when we look for the SQL Server name it will give the old windows machine name. You can see this in the image below. The new name is listed in Object Exporer, but the old name appears on the right as the result of the query.
To correct this, we need to first run sp_dropserver ‘Old server Name’ in the master database.
Next, we run sp_addserver ‘New server name’,'local’ in the master database. After running this, restart the SQL Server services.
Now check the new name of the instance by running select @@servername.
Now the SQL instance name has been changed and matches the windows name change and the minimum rights required to perform this action are in the setupadmin server role.
