Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Unanswered: Killing Remote Application

    Question:

    I want to restrict a user from logging in different workstations or creating a multi-sessions of my application. HOw can I remotely kill the application if he tries to login again considering that he has still live connection or open application in the same pc or another? Assuming the user has successfully logged on to the other pc, how can I send a message to his original opened application informing that connection is closed or something like.

    If you are familiar with Yahoo Messenger, you will understand my point.

    Im using VB6 and MSSQL 2000...

    Anybody who has an answer for this please help. As administrator, we normally prevent users from opening different sessions.

    Assuming I can kill a live connection using KILL (sp_id) in SQL Server, In VB app, how can I test the connection if its alive or not coz I might use a timer to check every second or a minute so a message box will appear saying that connection is killed remotely and the application terminates?


    declare @@user_id varchar(20)
    set @@user_id='TheUserIDLogged'
    sp_who @@user_id

    I also tried to do this but error occurs:
    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near '@@user_id'.

    How can i get the sp_id of this connection so that I can execute the
    KILL function?


    Any relevant idea is highly appreciated....Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do they login?

    What's your security model?

    And if you find multiple logins, which one do you pick to kill (Batai?)

    Code:
    USE Northwind
    GO
    
    CREATE TABLE tbl_sp_Who2 (
    	  SPID		int
    	, Status	varchar(255)
    	, Login		varchar(255)
    	, HostName	varchar(255)
    	, BlkBy		varchar(255)
    	, DBName	varchar(255)
    	, Command	varchar(255)
    	, CPUTime	int
    	, DiskIO	int		
    	, LastBatch	varchar(255)
    	, ProgramName	varchar(255)
    	, SPID2		int)
    GO
    
    INSERT INTO tbl_sp_Who2 EXEC sp_who2 active
    
    SELECT * 
      FROM tbl_sp_Who2 
     WHERE Login IN ( SELECT Login 
    		    FROM tbl_sp_Who2 
    		GROUP BY Login 
    		  HAVING COUNT(*) > 1)
    GO 
    
    
    DROP TABLE tbl_sp_Who2
    GO
    HTH
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    After opening the initial connection, I would test to see if this user already has open connections using the sp_who command passing the login. If a connection exists, reply with a message box and terminate the application. To test to see if your application is already running on a computer you can test that within vb as well.

  4. #4
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Follow up question

    Thanks for the effort....
    BUt can you tell me further how to pass the login in SP_WHO so I can get its sp_id and then execute the KILL?
    i tried this but an error occured....
    declare @@user_id varchar(20)
    set @@user_id='TheUserIDLogged'
    sp_who @@user_id

    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near '@@user_id'.


    Thanks in advance...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •