How to insert IP Address in data table dynamically

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » How to insert IP Address in data table...

How to insert IP Address in data table dynamically

Rate Topic

Display Mode

Topic Options

Author

Message

anamika.singh282

Posted Wednesday, July 28, 2010 5:15 AM

Forum 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 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.

Post #959945

Lowell

Posted Wednesday, July 28, 2010 6:45 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 11,609, Visits: 27,659

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #959989

« Prev Topic | Next Topic »

Permissions