When @@servername returns null

Background

My brother made a smirk remark at me a little while ago.  He said “You used this computer a lot and so how come @@servername is returning null“.

Servername is null

WhenServerNameReturnsNull

Information Sharing

Legacy

And, so I took the time to explain that @@servername is a bit legacy.  And, that there are more modern ways to determine the server’s name.

 

Modern

Issuing serverproperty, which has been available since at least v2005 offers more flexibility and insight.

 

Function Meaning Sample
serverproperty(‘servername’) The SQL Instance name HRDB
HRDB\PROD
 serverproperty(‘machinename’) Standalone Computer – The computer’s name

Clustered Computers – The Cluster name

 HRDBCLUS
serverproperty(‘ComputerNamePhysicalNetBIOS’) The computer’s netbios name.

For Clustered computer’s, the active node.

HRDB-01
HRDB-02

 

 

Remediation

sp_addserver

To fix his problem, we of course, can use the sp_addserver SP.

Syntax


exec sp_addserver 
          @server = [server] 
        , @local =  'local'   

Sample


exec sp_addserver @server = 'LABDB' , @local = 'local'

Effect
Please restart the SQL Instance for the change to effect.

sp_dropserver

Btw, if a wrong or an old computer name, had been returned we would of course have to disassociate using sp_dropserver.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s