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 insert IP Address in data table dynamically Expand / Collapse
Author
Message
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 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
Posted Wednesday, July 28, 2010 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 12,881, Visits: 31,811
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 »

Add to briefcase

Permissions Expand / Collapse