Retrieving IP address of current client's connection

MS SQL 2000:

I can retrieve "host" name of the current connection from "sysprocesses" system table(select host from sysprocesses). But is it possible to get the IP address of the client connection instead of host?

I could not find any stored procedures or extended procedures that would let me retrieve such information.

thank you in advance.

Hi,

there is no such call AFAIK. But you can use the follwing procedure to evaluate your IPAdress:

CREATE PROCEDURE getIPAdress

(

@.Hostname VARCHAR(255)

)

AS

SET NOCOUNT ON

CREATE TABLE #Results

(

Results VARCHAR(4000)

)

DECLARE @.Commandstring VARCHAR(300)

SET @.Commandstring = 'ping ' + @.Hostname

INSERT INTO #Results

EXEC master..xp_cmdshell @.Commandstring

Select DISTINCT SUBSTRING(Results,12,CHARINDEX(':',Results)-12) AS HostIpAdress from #Results

Where Results LIKE 'Reply From%'

DROP TABLE #Results

GO

In SQL Server 2005, you can sure use a more sophisticated method of .NET.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you for the response.
I had tried the method and it works.
But I do not think I should be trying to retrieve IP address for now just because of the fact that I have to run "xp_cmdshell".
|||

IN 2005 you can try:

select * from sys.dm_exec_connections

This will give you the IP Address of any TCP/IP connections.

Louis

|||

Thank you for also giving the solution for SQL Server 2005.

I am downloading and about to give SQL Server Express 2005 a try just for that functionality.

Great opportunity to move toward SQL Server 2005 if it works out :)

*** EDIT ***

I have installed SQL Server 2005 and tried out the query and it worked as I wanted. :)