1

You might find the question duplicate to these 2 links (Link1 and Link2) but they haven't been answered correctly. Let me brief you the question what i really want.

Looking For

I am looking for a way through which we can check if the provided SQL Instance Name is locally available or is residing on another remote computer.

My local SQL Server Instance name is Office-PC\SQLEXPRESS

Solutions i found which didn't work

1) Source : When i use the below code it says remote while the specified instance is present local.

SELECT Case when HOST_NAME()=SERVERPROPERTY('Office-PC\SQLEXPRESS') then 'local' else 'remote' end

2) I tried using the below code suggested in many comments on different websites. But it is showing incorrect output.

print HOST_NAME()    //OUTPUT : Office-PC
print @@SERVERNAME   //OUTPUT : HOME\SQLEXPRESS

3) Some people suggested to use the below command to get instance name which works perfectly but it doesn't tell us which of the instance is locally available.

SQLCMD -L

4) I also read somewhere (can't find the link) that we can get the Instance name from Registry itself.

That is all i could find.

If anyone has any other ways to find if the provided named SQL Instance is locally available, then please let me know.

Improve this question
4
  • I'm not clear what context you're in. 1 and 2 suggest you're currently in a SQL session, and you want to find out whether the SQL instance you're already connected to is local. But then, local to what??. 3 and 4 suggest you're interactively at a computer, and want to find out about a SQL instance that may or may not be on it. Which is it? Commented Jul 3, 2018 at 13:44
  • HOST_NAME() and @@SERVERNAME work for me. If I check on a remote connection I get two different results; if I check on the actual server where the SQL Server instance lives I get two identical results. But this is because the SQL Server instances where I work are named after the server they run on. So this comes down to configuration? Commented Jul 3, 2018 at 13:46
  • 1
    If you are connected to the server, SELECT client_net_address, local_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID should do. If client_net_address is <local machine> or client_net_address = local_net_address, then you are connected to a local server. Disclaimer: not thoroughly tested with all possible transports. Commented Jul 3, 2018 at 13:47
  • @JeroenMostert it worked perfectly. Please post it as an answer so i can accept it. Commented Jul 4, 2018 at 3:27

2 Answers 2

Reset to default
3

This is a little more complicated than I'd like, but HOST_NAME() or the server machine name aren't reliable, the following should do:

SELECT CASE 
    WHEN client_net_address IN ('<local machine>', '<named pipe>', local_net_address) THEN 'local' 
    ELSE 'remote' 
END AS [ConnectionType]
FROM sys.dm_exec_connections 
-- The parent_connection_id check covers MARS
WHERE session_id = @@SPID  AND parent_connection_id IS NULL

Disclaimer: not thoroughly tested with all possible transports. This works against local memory connections, LocalDB instances and TCP/IP connections, but it may not work correctly if you use the (rare) remote named pipes or VIA transports. These are disabled by default.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can use SERVERPROPERTY like this:

SELECT CASE WHEN HOST_NAME()=SERVERPROPERTY('MachineName') THEN 'local' ELSE 'remote' END

MachineName

Windows computer name on which the server instance is running.

For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

Improve this answer

3 Comments

i have used this code but it is returning "remote" i have tried using with machine name as well as SQL Instance Name
That's strange, I have tested it on few local and few remote instances. And it works just fine.
sorry mate but in my case the "JeroenMostert " answered correctly in the comments section.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.