Our software systems are funny and sometimes give us strange values. I don’t quite venture out trying to fiddle around with system level properties on my servers, but having said that, this query had come from one of my colleagues who was working on his SQL Server instance and he found something strange.
When working on the server and when an error occurs, I generally ask for the @@Version. This is a standard practice for me to do it because I don’t have to ask about the version installed, what service pack, CU update has been done on the server etc. This output would give me all this information in black and white.
As I was so used to this kind of output, my friend said that the value that is returned by his @@Servername was NULL. This was such a surprise and I wanted to see it in real life. He was kind enough to show me via Skype that this was indeed the case. This got me thinking and I needed to solve this trivial problem. I had gone to bed early as it was a lazy Sunday and I couldn’t put myself to sleep as this was plaguing my mind – I needed a solution. Finally, after an hour and getting some help from my SQL buddies, I found the deal to crack this problem. Strange but I am sure many of you have encountered this somewhere and were looking for a solution.
The @@ServerName refers to the local server that SQL Server is running on. This property is set during installation, but there are instances where @@ServerName may be NULL or may not be correct.
Running the following will show the current TSQL value for this property:
SELECT @@SERVERNAME;
GO
To Fix the Problem
If the @@ServerName property is NULL, running the following will fix the issue- but only after you restart the SQL Server instance. Yep, fixing this requires an outage.
EXEC sp_addserver '<LocalServerName>', local;
GO
If the @@ServerName property is incorrect, run the following to correct the issue:
EXEC sp_dropserver 'old_name';
GO
EXEC sp_addserver 'new_name', 'local';
GO
Though the solution to this was so simple, I did find it strange about this behavior. I am yet to come to terms this can every happen. If you ever encountered this error on your server, can you please let us know how you landed to this situation. I am eager to learn the cause for this rather than just looking at solution to the problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
I have seen this with automated SQL setups from images. In the past, I would find the servername to be incorrect when they sysadmin team configured an VM from an image that had SQL installed. But I’ve also seen the servername property be NULL. I always figured it had something to do with an automated setup or from an image. Never really investigated it further – I just did the drop/add server to correct it. In older versions of SQL it really messed up the maintenance plans when the servername was not the actual server name.
Thanks EdM for the note. This makes complete sense.
I have come across this issue following two scenarios. Once following an incorrect implementation using a template SQL Server installation in VMware and once where the server name was changed following a build for business reasons. I have since added it to one of my checks to ensure that @@servername is the same as the host name
Thanks for adding your comment. I think this aligns to my understanding better. Surprised to see these are indeed common :) …
This was the case for one of our servers whose machine name was changed after sql server 2000 was installed. My guess is that setting the server name property is part of sql server setup scripts, but not automatically updated if the name changes.
This happens on a cloned sql server
SQL Server instance name still showing null after sql server services restart (rebooted server also)
EXEC sp_dropserver ‘old_name’;
GO
EXEC sp_addserver ‘new_name’, ‘local’;
GO
This problem happen after windows update and server restart on my production environments. we already know the solution of this problem but not understand way this problem occur ?
Pinal did you know why this problem happen?
We had this problem because we cloned our azure vm (which had sql server installed) to create a new server and the newly created one had this issue and it was showing old server name for @@servername. Thank you for the solution, it helped a lot.
I just had this happen today. I was adding a 3rd instance of SQL 2014 on an existing dev server, but it has a different collation than the others. The installation gave errors, which I believe was maybe caused by an SSL cert problem and not being able to start the server towards the end of the install process. @@servername returns NULL and was unable to login as sa. However the contents of sys.servers is the same as the working instance (other than the instance name). Removing the instance and trying again… maybe has something to do with not using the latest installer?
We have experienced this problem with our servers that are part of Always On Clusters. All have been SQL Server 2014 Enterprise, (12.0.6329.1).
I have a requirement to change the SQL server name to its original local server name.
Select @@SERVERNAME is giving the different name. This the issue with most of the servers which are build on private cloud.
My requirement is, we need to change the
output of
Select @@SERVERNAME –old_name
to
Output of
SELECTSERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) [Machine Name] –new_name
Below is the query to change it, I need a custom query to change all wrong server names.
EXEC sp_dropserver ‘old_name’;
GO
EXEC sp_addserver ‘new_name’, ‘local’;
GO
We can run this query in CMS server for the servers which are with wrong names. Please assist.
Just came across this. Server was not an image or ever renamed. Someone created a linked server with the same instance name and with an Active Directory ID. That ID no longer exists, and doubt that was a contributing factor other than someone not knowing what were doing and performed a sp_dropserver on the local name. In sys.servers, the instance was no longer id=0, and [is_linked] was now 1.
Dropping the linked server and running the sp_addserver with local and a recycle corrected the issue.
Perhaps sp_dropserver should not be so compliant when removing the local instance as if it were any other linked server, like require the @local parameter just like sp_addserver.
Thanks Dave for this article, I just came across this as I was looking for a solution to my ”incorrect server name” problem.
When I first setup my new laptop the machine name looked like was “Laptop-C9SD8U35”. So when I installed SQL Server, the server name looked like “Laptop-C9SD8U35\SERVER_1”.
This looked very complex and untidy to my eyes so I changed my Machine name to a simple ‘ZENBOOK14’. The model name of the laptop.
Now, in SSMS, under object explorer the name of the server was updated to “ZENBOOK14\SERVER_1”. Perfect! However, Select @@ServerName still resulted in Laptop-C9SD8U35\SERVER_1.
Like an Idiot, I was going all bonkers as I was unable to setup a linked server due the irregular server/instance names.
I think, the system table (if I’m guessing right) was not updated correctly when I changed my machine name.
Finally,
EXEC sp_dropserver ‘LAPTOP-C9SD8U35\SERVER_1’;
GO
EXEC sp_addserver ‘ZENBOOK14\SERVER1’, ‘local’;
GO
fixed my problem.
Thanks.