How to find IP Address of Sql Server?

Hi Experts,

I want to find ip address of my sql server and when googled i got  this query
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

Open in new window


.But when i run the query i got the error as

CONNECTIONPROPERTY' is not a recognized built-in function name.
. How to solve this ?
karthik80cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version of SQL Server are you using?
The functions is available since MSSQL 2008.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can get the same information by running the following:
select distinct net_transport, protocol_type, auth_scheme, local_net_address, local_tcp_port, client_net_address
from sys.dm_exec_connections

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karthik80cAuthor Commented:
Hi Vitor Montalvão,


We are Using SQL 2012  edition
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jason clarkDBA FreelancerCommented:
You should run this query to find out the IP address
SELECT CONNECTIONPROPERTY('local_net_address') AS [IP Address Of SQL Server]
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID

Open in new window

You can also create a stored procedure to get IP address of your SQL Server.

create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
  set @ip = NULL
  Create table #temp (ipLine varchar(200))
  Insert #temp exec master..xp_cmdshell 'ipconfig'
  select @ipLine = ipLine
  from #temp
  where upper (ipLine) like '%IP ADDRESS%'
  if (isnull (@ipLine,'***') != '***')
  begin 
    set @pos = CharIndex (':',@ipLine,1);
    set @ip = rtrim(ltrim(substring (@ipLine , 
    @pos + 1 ,
    len (@ipLine) - @pos)))
  end 
drop table #temp
set nocount off
end
go 

Open in new window

karthik80cAuthor Commented:
Hi Jason clark

Am checking with your query  and let you know the result
Steve WalesSenior Database AdministratorCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Vitor Montalvão (https:#a41470353)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

sjwales
Experts-Exchange Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.