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:
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;
If the @@ServerName property is incorrect, run the following to correct the issue:
EXEC sp_dropserver 'old_name';
EXEC sp_addserver 'new_name', 'local';
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)