none
Taking the clients IP address in MS SQL Server 2000

    Question

  • Hi all

    I need to specify the properties of the clients of my database which is under the MS SQL Server 2000. I want to know the IP address of each connection and the Date, Time and transaction of that connection.

    We have th EVENTDATA() function in MS SQL Server 2008 but I did not find anything for doing that in MS SQL Server 2000.

    Please help me to do that.

    Thanks
    Saturday, July 25, 2009 12:47 PM

Answers

  • The only thing available is what you find in sysprocesses (machine name and MAC address). Possibly, you can use either or both of those and call out to the OS (using some programming)...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Zongqing Li Thursday, August 20, 2009 9:44 AM
    Saturday, July 25, 2009 2:44 PM

All replies

  • The only thing available is what you find in sysprocesses (machine name and MAC address). Possibly, you can use either or both of those and call out to the OS (using some programming)...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Zongqing Li Thursday, August 20, 2009 9:44 AM
    Saturday, July 25, 2009 2:44 PM
  • Use the following procedure to get the IP

    create Procedure sp_get_ip_address (@ip varchar(40) out)
    as
    begin
    Declare @ipLine varchar(200)
    Declare @pos int
    set nocount on
    set @ip = NULL
    Create table #temp (ipLine varchar(200))
    Insert #temp exec master..xp_cmdshell 'ipconfig'
    select @ipLine = ipLine
    from #temp
    where upper (ipLine) like '%IP ADDRESS%'
    if (isnull (@ipLine,'***') != '***')
    begin
    set @pos = CharIndex (':',@ipLine,1);
    set @ip = rtrim(ltrim(substring (@ipLine ,
    @pos + 1 ,
    len (@ipLine) - @pos)))
    end
    drop table #temp
    set nocount off
    end
    go

    declare @ip varchar(40)
    exec sp_get_ip_address @ip out
    print @ip
    Wednesday, July 29, 2009 7:25 AM
  • If you are going to be working with IP addresses give a look at the PARSENAME function.  This function is not intended to be used with IP addresses, but it sure comes in handy for breaking the IP address into sections.


    Kent Waldrop

    Wednesday, July 29, 2009 11:51 AM