0

I recently checked this in my SQL server instances:

SQL Server 2012 EE:

-- get current SQL Server name\instance name
  SELECT @@SERVERNAME
-- get current machine name and instance name
  SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

And the results show NULL value for instance name for some of the instances.

I did go through Instance name NULL

Sample output for one of my servers for SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

was

'Class024.dev.com', Null

Should I change the Instance name? Is it necessary or does it returns NULL if the instance is the default one?

closed as unclear what you're asking by ypercubeᵀᴹ, Andriy M, RolandoMySQLDBA, mustaccio, Max Vernon Jan 12 '16 at 4:30

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Did you performed any of these actions that leads you to NULL Server Name? 1. The computer was renamed. 2. An image was taken of the SQL Server computer, and then copied onto another computer. 3. The sp_dropserver stored procedure was run for the local SERVERNAME. ? – AA.SC Jan 9 '16 at 14:01
  • I dont remember exactly. But there are chances that the image was copied onto another computer.. – tesla747 Jan 9 '16 at 14:02
  • 2
    InstanceName will return NULL for default instances. Can you show some actual examples of @@SERVERNAME where InstanceName returns NULL? – Aaron Bertrand Jan 9 '16 at 14:27
  • 2
    I'm still having a hard time understanding what change we're talking about. From what I know so far, my guess is that InstanceName is NULL because these are not named instances. How would you change that? – Aaron Bertrand Jan 9 '16 at 15:15
  • 3
    This question lacks enough detail to get any actual help with. It is not even stated what the expected instance name would be. Please update the question to state clearly what you are expecting the ServerName\InstanceName value to be, as well as the output from this query: SELECT name, data_source, @@SERVERNAME AS [@@SERVERNAME], SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY ('InstanceName') AS [InstanceName] FROM sys.servers WHERE server_id = 0;. Thanks. – Solomon Rutzky Jan 9 '16 at 16:30
1

It does return 'Null' if you've only got a default instance. I tried it in SQL 2005 and SQL 2008R2. Otherwise, @@SERVERNAME should be the same as ServerProperty('MachineName') + '\' + ServerProperty('InstanceName') Tested this also in SQL 2005 and SQL 2008R2.

Not the answer you're looking for? Browse other questions tagged or ask your own question.