-4

In the bottom-right hand side of SSMS's main screen and to the left of where it shows my user-name and the name of the database that I'm in, it gives the name of the server that I'm on. This string is the same as what I put in the "Server name" field when connecting to the server when I first open SSMS and is also shown in the Object Explorer. How does SSMS determine this name? I've tried

SELECT 
  HOST_NAME(),
  @@SERVICENAME,
  DB_NAME,
  @@SERVERNAME,
  SERVERPROPERTY('InstanceName'),
  SERVERPROPERTY('MachineName')

but only SERVERPROPERTY('MachineName') returns a substring of what SSMS shows. The rest have nothing at all to do with the string in question.

5
  • 3
    "This string is the same as what I put in the "Server name" field" - so why does it need to "grab it" from anywhere? You have already told it Jan 20 at 21:14
  • @MartinSmith There is always value in knowing how to programmatically retrieve a useful value.
    – J. Mini
    Jan 21 at 0:08
  • 3
    @J.Mini Your reply to Martin Smith makes it sound like you misunderstood what he was saying. Is it clear that it's likely stored in the application's Memory because you told it what the server name is?
    – J.D.
    Jan 21 at 1:05
  • @J.D. If you can prove that is where SSMS pulls the string from, then that's an answer to the question.
    – J. Mini
    Jan 21 at 8:18
  • 5
    The only way of "proving" it would be too decompile the SSMS assemblies and look. Personally I have zero motivation to do this. You have already told it the server name to connect to. Object Explorer and New Query windows themselves make a connection to the database using this information so I'm not getting why you think it is a mystery that they are able to display the same information Jan 21 at 10:11

2 Answers 2

1

You accepted Alocyte's answer, but you want more evidence. Let's give it a try.

I have a SQL Server. The server's name is 100002-DB28. The instance name is Fam7_Test. We'll start with ...

The control case

Connecting to it as 100002-DB28\Fam7_Test:

SSMS connect dialog

We can see that select @@servername gives us the server/instance name, and the status bar of SSMS shows the same.

SSMS query results

Variation #1

I have a DNS alias (a CNAME) for that server, calling it DataNREngineTest. So I can connect to it as DataNREngineTest\Fam7_Test:

SSMS connect dialog

This time, the SSMS status bar reflects the name I used in the Connect dialog, even though the result of select @@servername shows that it's still the same server as before.

SSMS query results

Variation #2

Let's use the IP address. I can connect to it as 172.16.100.207\Fam7_Test:

SSMS connect dialog

This time, the SSMS status bar reflects the IP address I used in the Connect dialog, and the result of select @@servername shows that it's still the same server as before.

SSMS query results

Variation #3

Let's use the port number instead of the instance name. I can connect to it as 172.16.100.207,52043:

SSMS connect dialog

This time, the SSMS status bar reflects the IP address and port number I used in the Connect dialog, and the result of select @@servername shows that it's still the same server as before.

SSMS query results

Variation #4

It's not much different from the IP address case, but let's run SSMS locally on the server and use the local loopback address of 127.0.0.1. I can connect to it as 127.0.0.1\Fam7_Test:

SSMS connect dialog

This time, the SSMS status bar reflects the loopback address and port number I used in the Connect dialog, and the result of select @@servername shows that it's still the same server as before.

SSMS query results

Variation #5

This one is not much different than the DNS alias of Variation #1, but it is easier for others to reproduce if they don't have access to make DNS changes. Let's use a Hosts file entry pointing demo4dba_se to the SQL server. I can connect to it as demo4dba_se\Fam7_Test:

SSMS connect dialog

This time, the SSMS status bar again reflects the name (from the Hosts file) that I used in the Connect dialog, and the result of select @@servername shows that it's still the same server as before.

SSMS query results

That's six different cases where the SSMS status bar shows whatever I entered in the Connect to Server dialog.

Summary

What Alocyte said, but with pretty pictures.

1

Derived from you connection string. Try target public IP, or URL, or internal IP, or netbios name... will mirror whatever you use.

2
  • Do you have any evidence for this? Even a series of screenshots would do.
    – J. Mini
    Jan 29 at 15:54
  • Hi - can't expose IP / URL - try changing your connection to the same server and observe: if you are locally connecting, try 127.0.0.1, then ., then machine name, then public URL (assuming its configured for internet access)...
    – Alocyte
    Jan 31 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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