Post reply

How to insert IP Address in data table dynamically

  • July 28, 2010 at 5:15 am

    #227257

    i have a store procedure for user login which i am calling in my ASP.NET code.And now i want that when user log, the IP Address of that user's system and date time of system will get saved into another table.i should be saved in 192.167.1.78 format. i have gone through your article but thats not fullfiling my need.please help me how can i do this.i tried this code which is not working. one thing i m not understanding as i am not very usuall with store procedure coding.what does exec dbo.xp_cmdshell'ipconfig' stand for.i mean what is "xp_cmdshell" and which dbo it is executing.

    My code is:

    ALTER PROCEDURE [dbo].[User_Login]

    @username varchar(20),

    @pword nvarchar(15),

    @ipadress varchar(40) output,

    @rs int output

    AS

    SELECT @rs = COUNT(*) FROM Users WHERE username=@username AND pword=@pword

    BEGIN

    DECLARE @ipLine varchar(200)

    DECLARE @pos int

    SET NOCOUNT ON

    SET @ipaddress = NULL

    CREATE TABLE LoginDetail(ipLine varchar(200))

    INSERT INTO LoginDetail exec dbo.xp_cmdshell'ipconfig'

    SELECT @ipLine = ipLine FROM LoginDeatil WHERE upper (ipLine) LIKE '%IP ADDRESS%'

    IF (ISNULL (@ipLine,'***') != '***')

    BEGIN

    SET @pos = CharIndex(':',@ipLine,1);

    SET @ipaddress = RTRIM(LTRIM(SUBSTRING (@ipLIne,@pos + 1,

    LEN(@ipLine)-@pos)))

    END

    DROP TABLE LoginDeatin

    SET NOCOUNT OFF

    END

    GO

    i have to call this SP in my ASP.NET code.i dont want to display ip address and date time in front end.Only want to store ip and date time.

  • July 28, 2010 at 6:45 am

    #1199192

    ipconfig will return information about the SERVER's NIC card...it would not have any information about the connecting users. that code might seem to work if your SQL server is also your development platform....if they are different, it would obviously not be accurate.

    one of the data managment views (DMV) as the column sys.dm_exec_connections which is the connecting user's IP address; I'm not sure if you can use it in an logon audit trigger, I'd have to test that, but you can certainly use it inside a stored procedure or other statement after they've connected.

    select

    host_name(),

    app_name(),

    client_net_address

    from sys.dm_exec_connections where session_id = @@spid

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply