2

How to select IP address of server only from query in SQL Server 2005 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rajni Patel
  • 31
  • 1
  • 2

3 Answers3

1

Try this:

SELECT CONNECTIONPROPERTY('local_net_address') AS local_net_address,

Or: 

SELECT SERVERPROPERTY(N'MachineName');

Or:

SELECT @@SERVERNAME;
CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
  • I need IP Address not the name of the Server when I am executing "SELECT CONNECTIONPROPERTY('local_net_address') AS local_net_address," getting following error 'CONNECTIONPROPERTY' is not a recognized built-in function name. – Rajni Patel Apr 11 '13 at 08:22
  • You can resolve host names: http://stackoverflow.com/questions/967684/resolve-hostnames-with-t-sql – CloudyMarble Apr 11 '13 at 08:35
  • I want ip-address of server not of the current client .I am not able to get the IP Address instead of it every where i am getting only server name....but i need IP address.. – Rajni Patel Apr 11 '13 at 08:39
  • Can not you make a stored procedure which resolves the hostname and return this back, and call the stored rpocedure from the clinet ? – CloudyMarble Apr 11 '13 at 08:41
  • So that means if i want to know "the IP Address of Server from query which runs on SSMS of server" is not possible ??? – Rajni Patel Apr 11 '13 at 08:46
  • What do you get when you resolve the name using xp_cmdshell on SSMS? – CloudyMarble Apr 11 '13 at 08:55
  • I want only IP so that i can put that value in temp table...when i am using xp_cmdshell getting all the details ... – Rajni Patel Apr 11 '13 at 09:02
  • select Local_Net_Address FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID Is this right ??? – Rajni Patel Apr 11 '13 at 09:18
  • 1
    When i run this i get NULL. – CloudyMarble Apr 11 '13 at 09:22
  • local_net_address is null when the session is connected locally – gordy Aug 13 '14 at 02:16
0

Here you go with the help of function:

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;
END
Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
0

This is the best solution I found, I hope it helps you

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))   
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'  
,local_tcp_port  AS 'PortNumber' 
 FROM SYS.dm_exec_connections 
 WHERE local_net_address IS NOT NULL
 AND session_id IS NOT NULL
 GROUP BY LOCAL_NET_ADDRESS,local_tcp_port
Norbert C
  • 1
  • 1