Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to insert IP Address in data table dynamically Expand / Collapse
Posted Wednesday, July 28, 2010 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 15, 2010 5:39 AM
Points: 6, Visits: 22
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 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


SELECT @RS = COUNT(*) FROM Users WHERE username=@username AND pword=@pword
DECLARE @ipLine varchar(200)
DECLARE @pos int
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,'***') != '***')
SET @pos = CharIndex(':',@ipLine,1);
SET @ipaddress = RTRIM(LTRIM(SUBSTRING (@ipLIne,@pos + 1,
DROP TABLE LoginDeatin

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.
Post #959945
Posted Wednesday, July 28, 2010 6:45 AM



Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 14,557, Visits: 38,448
ipconfig will return information about the SERVER's NIC 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.

from sys.dm_exec_connections where session_id = @@spid


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!
Post #959989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse