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...ThanksHow do they login?

What's your security model?

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

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

Keep remote SQL data tables updated

Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.

Any ideas on how to keep the data updated on the website?

How updated do you want?|||Within an hour perhaps. I have a remotely hosted sql server at discountasp.net and a sql server in my domain. The hosting provider does not allow dts or replication.|||

I would say create a web service at your hosting provider. Then create a service you can run on your local sql 2000 machine that does the pull/push at intervals. Just make sure that all your important tables have auto-incremented id fields so you know what you are missing since last pull/push.

It sounds like the hosting provider is the master table for orders, and your local sql server is the master for products. Create a trigger on the ISP's order table that records all insert/delete/updates to that table to an auditOrders table that contains an id field (Autoincrement int), a changetype field (char) constrained to 'I','U', or 'D', a ChangeTime field (datetime with default of GetUTCDate()), and a field for every field in Orders. Make a similiar trigger on your local products table going into auditProducts. Then create a webservice at the hosting provider that has 3 methods:

public function GetNewOrders(LastId as integer) As Dataset

public function GetLatestProductChangeID() as integer

public sub SaveProductChanges(ds as dataset)

Make a copy of auditProducts and auditOrders at the other location. To pull changes to orders, select the highest id from your local auditOrders table. Then call GetNewOrders with that id. Take the dataset and record by record (It should be in id order) start a transaction, perform the Insert/Update/Delete on the local table, and then insert the record into the local audit table, and commit the transaction. To push Products, call the GetLatestProductChangeID, then create a dataset containing all the records from the local auditProducts table where the id is larger than the id you got from the webmethod, and call SaveProductChanges with the dataset.

Alternatively from having to do the transaction, and manually record the record into the audit table, you can also copy the triggers from the other side, and it (SHOULD) recreate the record, all except for the ChangeTime which will be different obviously.

|||

Take a look at my Remote SQL data provider, WebSql Data Provider, for secure, efficent access to SQL Server over HTTP, as simple as using the native SQL provider in System.Data.SqlClient namespace.