How to find IP Address of Sql Server?

karthik80c
karthik80c used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Which version of SQL Server are you using?
The functions is available since MSSQL 2008.
IT Engineer
Distinguished Expert 2017
Commented:
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

Author

Commented:
Hi Vitor Montalvão,


We are Using SQL 2012  edition
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jason clarkDBA Freelancer

Commented:
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

Author

Commented:
Hi Jason clark

Am checking with your query  and let you know the result
Steve WalesSenior Database Administrator

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial