When a user on my site registers and his data is inserted into a table on sql server 2005 I need to know from which iis server the user arrived - not the users ip address. (there are 2 different server addresses under a server in iis from which the data could have been received). I then need to get the address and insert it into another table.
How can I receive this address?
Any help would be greatly appreciated.
Use the HOST_NAME() function
EDIT: OP clarified that he wants IIS server IP address.
Can you resolve the name to an IP using a lookup table? Presumably the IIS servers don't change that dynamically?
EDIT: some code
If you've got xp_cmdshell enabled you could try something along the lines of
declare @hostip varchar(50) declare @hostname varchar(50) declare @cmd varchar(8000) set @hostname = HOST_NAME() set @cmd = 'ping -n 1 ' + @hostname create table #temp (pingoutput varchar(max)) insert into #temp exec master.dbo.xp_cmdshell @cmd select @hostip = substring(pingoutput, charindex('[',pingoutput)+1, charindex(']',pingoutput)-charindex('[',pingoutput)-1) from #temp where left(pingoutput,7) = 'Pinging' select @hostip drop table #temp
replace the @hostname with whatever you have to find the IP of a given name, or use as-is to get the host ip
EDIT: some further thoughts
The 'ping' method to get the IP address of HOST_NAME() may not work if your web servers are in a DMZ
If it is IIS web logs that you are trying to import, are you aware of the [s-ip] field you can add to the W3C extended log format. This would add the web server ip to the log entries.