Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
awaisahmad435
Starting Member
11 Posts |
 Posted - 2011-05-28 : 01:54:31
|
| Aoa. Im new to sql server 2005 and Visual Basic 2008. Actually i have developed an application. On every form in vb program i have put formclosed event to update a field value which is bound with dataset while form is closed. This is working perfect, but problem is that if my application crashes or system restarts while electricity shutdown, formclosed event doesn't execute. so what to do to cope this problem......is there any way in vb 2008 to solve the problem???or any script in SQL Server 2005 that detects that particular illegally closed application and update that value which was the duty of vb application to update.Is there any way in Sql Server 2005 that I can check the status of client connection whether the connected vb application is running or crashed. if crashed then update a particular table's field value. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-28 : 05:53:22
|
| You shoud design the application to cater for a crash or just a loss of connection to the database. Relying on a controlled exit won't work.Have you also designed for a crash at any point in the application? That usually means making sure that no updates are made until all the data is available then updating in a transaction.As to detecting that the applcation has cashed - only possible if the application holds a connection - which again is probably bad design. You could have a job that checks the last action for a session (user?) and takes action if nothing has been recorded after a certain time - that would mean recording the click events or havig a heartbeat process in the app.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jfarrugia
Yak Posting Veteran
55 Posts |
Posted - 2011-05-28 : 07:49:00
|
| I had worked on something similar in the past, not exactly the same scenario as yours, however I needed to create some monitoring app which ensures that certain services, windows apps etc are up and running. I had divided the system in two modules:A) a dll included in the app (in your case the VB app) - basically a simple dll responding to heartbeat requests from the server. Very simple heartbeat mechanism via sockets. B) a server app (being service based or not) - which sends a heartbeat requests every so often to the clients (in your case the VB app). Server sends a packet down to client, client responds with a different packet. If packet received, then all is fine, if not, then the app is offline.Hope the concept is clearWhere software development knowledge meets the reader |
|
|
|
awaisahmad435
Starting Member
11 Posts |
Posted - 2011-05-29 : 01:22:33
|
| But jfarrugia i have the same idea, but dont know that how to implement it. how to make that heartbeat process on server. actually i m new to sql server, so plz guide me to do it... |
|
|
|
awaisahmad435
Starting Member
11 Posts |
Posted - 2011-05-29 : 01:49:26
|
| Actually i need to use Pessimistic Locking with strongly typed dataset...so i have designed my own record locking mechanism that works when a user click edit button. Means when a user clicks the edit button on particular record, other users wont be able to update the record.I have used a "flag" string type field in table. when a user clicks on edit button. flag is set to "USE" and updated in database. means edit button disables for other users. and when the particular user done his works and click on save or cancel button. flag is set to "FREE" and updated in database. so now that particular record is free for other users.My mechanism is working perfect. For Example. A user is working on particular record and clicked Edit button. that means flag is set to "USE". so that particular record is locked for other users. now the particular user's application crashes. what happened. now the Flag is set to "USE" forever.that the deadlock. because i have set Flag to "Free" on Cancel and Save button and on FormClosed event. but unfortunately all three options will not execute because the user has crashed......i have discussed all my views however if ur goodself require any further explanation plz ask me to explain. plz help me to solve it... or suggest me some other best method for pessimistic locking in Strongly Typed dataset.Thanks is advance |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-29 : 10:01:31
|
| Are you holding a connection?If so then if you record the spid and logon time (from master..sysprocesses) then when another user wants the record then check sysprocesses again - if there is no row with that spid and loon time then you know that the application has crashed and the lock should be released. I've used this method a number of times successfully - sometimes using the active connection and sometimes using a deicated connection for the locking from the app which allows transactions to be implemented on other connections.It needs a client server system though - it fails if you want a service oriented or distributed implementation and won't be as scalable.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
Strange Problem