Scott D. Jacobson (7/30/2012)
Lowell, just out of curiosity, what would the result set look like on say a clustered SQL Server instance compared to standalone? I don't have the ability to check this on a clustered instance and I'm curious if the results are different. Perhaps you have a link to a BOL article that explains in more detail?
Scott you can see a lot of variation in there, as the tcp_ip addresses can be different for each connection, whether you have your server listening to more than one IP address, etc. and of course depending on if you are connecting locally (named pipes makes the ip address for the server return null)
when i connect to my known 2008 cluster, these are the results, which is the clusters IP address of *.235 , and not the node IP (which are *. 230 and *.231, respectively..i seem to remember a witness is on *.232
net_transport protocol_type auth_scheme local_net_address local_tcp_port client_net_address physical_net_transport
TCP TSQL SQL 192.168.0.235 1433 192.168.0.55 NULL
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!