sql server programming: how to get the ip address of client connection (or any other specific
machine no) to now form which physical machine the connection was
made.
Rajesh

SELECT @ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@@spid
-
Shiju

[quoted text, click to view]

If you are on SQL Server 2000 take a look at this SP

create proc get_hostip (@spid int = NULL)
as
set nocount on

declare @host varchar(100)
declare @ip varchar(15)
declare @cmd varchar(200)
declare @temp varchar(255)
create table #ip(iptext varchar(255))

If @spid is null select @host = host_name()
else
select @host = max(hostname)
from master.dbo.sysprocesses
where spid = @spid

if @host is not null
begin
set @cmd = 'ping -n 1 ' + @host
insert #ip exec master..xp_cmdshell @cmd
select @ip = ISNULL(substring(iptext,(charindex('[',iptext)+1),
(charindex(']',iptext)-(charindex('[',iptext)+1))),'')
from #ip
where charindex('[',iptext)>0
end

drop table #ip
select NULLIF(rtrim(@host),'') as 'Hostname',
rtrim(@ip) as 'IP_Address'

return



[quoted text, click to view]