Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get IP address from sql server 2000 Expand / Collapse
Author
Message
Posted Sunday, March 23, 2008 7:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 29, 2009 5:02 AM
Points: 50, Visits: 72
I'm trying to write trigger for audit trail purpose.

and want to retrieve the IP address of the client that do the change in database

Is it possible to get the IP address in trigger command

or I have to write store procedure for this by myself instead make use of TRIGGER


THANKS IN AVANCE
Post #473301
Posted Sunday, March 23, 2008 2:02 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
On SQL2005 you can get this from select * from sys.dm_exec_connections in the client_net_address column assuming that their net_transport is TCP.

On Sql2000 it is a challenge. It appears that for most remote connections, the first connection/session from that client will have the MAC address coded in the net_address column of sysprocesses. Than you could use xp_CmdShell 'ARP -a' if your SQL server is also a domain controller to list the current cache of MAC to IP mappings. If it is not a domain controller, well ... we are already beyond the limits of what I know. Someone else will have to take it from here.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #473316
Posted Tuesday, January 12, 2010 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 20, 2010 10:39 AM
Points: 2, Visits: 6
Were you able to resolve this issue? I am looking for a solution to this issue as well..
Post #846220
Posted Wednesday, January 20, 2010 5:34 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 5:07 AM
Points: 712, Visits: 104
Hey Barry,

Tried this and it works for me.

Thanks for the Solution
Richellere
Post #850388
Posted Wednesday, January 20, 2010 1:42 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Thanks Richellere, glad I could help someone ...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #850821
Posted Wednesday, May 12, 2010 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 5:10 AM
Points: 1, Visits: 0
To find the Ip address, i made use of this site http://www.whoisxy.com/ & gain solution ..
It provides me nice solution in free cost ..
Post #920337
Posted Monday, November 01, 2010 6:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 26, 2013 11:44 PM
Points: 5, Visits: 112
Thanks Barry,
You pointed me in the right direction, in regards to sQL2000 I was able to use:

xp_CmdShell 'ipconfig|find "IP Address"'

I had to insert the output into a table variable and use RIGHT(Rtrim(ipaddr),15) to get just the "IP Address"
Post #1014230
Posted Tuesday, March 08, 2011 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 3:43 AM
Points: 2, Visits: 2
use this query for trigger or procedure it's get the ip address in sql server


declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip
Post #1074798
Posted Tuesday, March 08, 2011 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 3:43 AM
Points: 2, Visits: 2
use this query for trigger or procedure it's get the ip address in sql server


declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip
Post #1074799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse