Hi All:
Is there a way to tell if I am connected to the server on the computer I am using to connect with?
I want to determine whether the connection is to the local server or a remote server.
Thanks.
Doug.
Doug,
The name of the server should appear in your 'Object Explorer' window. This will tell you whether you are connected to your local instance or a remote server.
Good Luck...
'Avarus animus nullo satiatur lucro - A greedy mind is satisfied with no (amount of) gain
My fault, should have been more specific.
I am connecting to the server using ADO; is there any command, TSQL, etc that will let me know if the connection is to the local server?
|||Execute the query (CommandText):
SELECT @.@.SERVERNAME
The return value will be the name of the Server you are connect with when the query is executed.
|||Hi Arnie:
Perhaps I'm missing the point here... but would not that only give me the name of the local server, not the fact that I am actually sitting at the same workstation?
I did find another TSQL function called HOST_NAME() -- but I believe that returns the workstation name where the server resides, not the workstation I am sitting at?
So, I can get the host name and servername, but what I need is to know if there is a way to determine the I am physically at the workstation that hosts the server?
|||I did some more testing.
I connected to a remote machine and the host_name() returned the workstation I was physically at, and the @.@.servername returned the name of the local server on the host machine (if there is no local server then it would return null, right?).
So it looks to me that those functions alone will not work, as I need to know if I was connected remotely (which was the case) and they did not seem to help.
Unless... what if I compared the DataSource of the connection string to the servername?
If the hostname = servername, and the datasource = servername, then I am connected locally?
Actually, now that I think about it... I don't even need hostname, but the servername.
If the datasource = servername, then I am connected locally, right?
|||You are on the right track.
HOST_NAME() returns the client computer name.
@.@.SERVERNAME returns the SQL Server computer name/instance.
If the computer names don't match, the computers are different.
HOWEVER, the HOST_NAME() could be [MyComputer] and the @.@.SERVERNAME could be [MyComputer\SQLExpress] -a Named INSTANCE of SQL Server running on the computer [MyComputer]. So you would need to check for a backslash [\], and if present, use the portion of the @.@.SERVERNAME to the left of the backslash to compare with HOST_NAME().
DataSource = @.@.SERVERNAME 'should' always match. DataSource is the part of your connection string that directs the connection to a Server.
|||Got it, thanks!
|||Doug,
If you have Alerts on, then you will get this message.
I was half asleep. You can also use SERVERPROPERTY('MachineName') on the SQL Server. It will return the computer name without the Instance name -so you will NOT have to parse @.@.ServerName.
Just check
Code Snippet
IF host_name() <> serverproperty('machinename')
BEGIN
PRINT 'Remote Connection'
END
ELSE
BEGIN
PRINT 'Local Connection'
END
