SQL Records disapeared

[rPahulje]

Hi
I have (MS)SQL database (7) and VB client. Almost every commands on database are executed through stored procedures. Each stored procedure begin transaction on top and commit or rollback at bottom. Aplication has been fully testest and everything has worked normaly (as it should) in our development environment(our client and server mashine). When we installed application on client environment(their client and sever mashine) at start everything worked normal. But sometimes after user closed application records in database disapeared. This data records was 100% in database (these are many records in many related tables, not just in one table).
I'm think it maybe something wrong with their server or server software or thir network.

If anyone seen something like this or have any idea or solution, comment, would be greateful.

THX,
Rok

[Senecio]

Hi Rok,

I am experiencing the same problems myself lately!! I'm using an Access database and VB Client. It seems like my problems started from the point where I began to use Global Disconnected Recordsets to increase application speed on the client machine; At startup of the app I take snapshots of the data from the database (disconnected recordsets), so everytime a user has to access the information, it doesn't take so much time, because the data is in memory, and datatransfer over their (MUCH TOO SLOW!!!) network is kept to a minimum. Ever since this change they report that some of their data seems to be disappearing! I cannot recreate this phenomenon in development environment nor on test-pc's where I work. I really don't know what could cause this strange behaviour, but I'm looking for a solution in the area of their network-environment; I use a Microsoft Backoffice Server, and they use Novell. It seems unlikely to me that this should be a real problem, but this is the only difference in environment I can think of.

Good luck in finding a solution to your (and my) problem, and if you find one, please let me know!

Greetz,


Senecio

[Cakkie]

My guess is that a transaction wasn't finished before the application terminated. All changes made to records during a transaction are visible to the connection that started the transaction, so the records appear to be there. However, if the transaction isn't explicitly committed using COMMIT TRANS, the transaction will rollback when the connection is closed (in this case when the program ended).

Doublecheck all the transaction, both at SQL Server level as at VB level. It could be that you are issuing the .BeginTrans method over a connection, but for some reason (typically errorhandlers, goto's and exit sub/function statement) forget to either commit or rollback. This way, everything else that goes over the connection will be in 1 big transaction, eventually being rolled back.

Tom Cannaerts
slisse@planetinternet.be
Moderator on http://www.vbcodelibrary.co.uk/board

A bottomless pit, I'm sure it came with the place, who would dig one on purpose?