SQL SERVER – Fix @@ServerName Property Value When Incorrect

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.

SQL SERVER - Fix @@ServerName Property Value When Incorrect error 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)

,
Previous Post
SQL Authority News – 100 Million Views and Onwards
Next Post
SQL SERVER – Database Mirroring login attempt failed – Connection handshake failed

Related Posts

14 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.

    Reply
  • SQLDBAwithTheBeard
    August 24, 2015 11:34 am

    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

    Reply
    • Thanks for adding your comment. I think this aligns to my understanding better. Surprised to see these are indeed common :) …

      Reply
  • 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.

    Reply
  • This happens on a cloned sql server

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • Charles Blankenship
    April 16, 2020 9:46 pm

    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).

    Reply
  • 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.

    Reply

Leave a Reply

Menu