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 ?

Microsoft SQL ServerDatabases

Last Comment

Steve Wales

8/22/2022 - Mon

Vitor Montalvão

2/18/2016

Which version of SQL Server are you using?
The functions is available since MSSQL 2008.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

2/18/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

karthik80c

2/18/2016

ASKER

Hi Vitor Montalvão,

We are Using SQL 2012 edition

Jason clark

2/18/2016

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

karthik80c

2/21/2016

ASKER

Hi Jason clark

Am checking with your query and let you know the result

Steve Wales

7/5/2016

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