2008 R2 - ConnectionProperty system function is missing.

Question

2008 R2 - ConnectionProperty system function is missing.

johng-842937

SSC Journeyman

Points: 76
More actions
August 16, 2017 at 9:57 am

#339321

Trying to do updates to a client DB we have discovered that the System Functions -> Configuration Functions ConnectionProperty() is not present in any of the database instances on this server. How does one go about adding that to the server?
Thanks in advance,
John G.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

johng-842937 - Wednesday, August 16, 2017 9:57 AM
Trying to do updates to a client DB we have discovered that the System Functions -> Configuration Functions ConnectionProperty() is not present in any of the database instances on this server. How does one go about adding that to the server?
Thanks in advance,
John G.

You don't really and having it missing is very strange.
Did you try having a sysadmin executing something using that function - just something like:
SELECT CONNECTIONPROPERTY('net_transport')
Are you sure it's not just hidden through Tools - Options - Environment - Startup. I'm not even sure if hiding system objects would do that - just another thought.
Did you run DBCC CHECKDB?

Sue

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

I've never heard of it being unavailable. I don't think it requires any special permission to query it. Does the server work at all?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323504 More actions · Answer 3

I don't know that ConenctionProperty is actually database functionality . function. it's server functionality related to the connection, right?

this works in SQL2008 and above, and I just checked, and it doesn't get affected by compatibility level either:

SELECT 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'

Lowell

--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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

johng-842937 - Wednesday, August 16, 2017 9:57 AM
Trying to do updates to a client DB we have discovered that the System Functions -> Configuration Functions ConnectionProperty() is not present in any of the database instances on this server.

What error are you get when you try and use it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass