Missing SQL Server records

[MikeWiz9]

I have an application using an Access front end with a SQL server 2000 backend. Just recently we noticed that some records are mysteriously disappearing from SQL Server.
The records are entered through Access via ADO calls. The records are in the SQL database, but then minutes or hours later they seem to be erased.
Just wondering if anyone has come across this behavior before. Is this a "feature" of Access or SQL Server? And if so, is there a patch/fix for it?

Thanks

-Mike

 

[JayKusch]

In the production database realm ... the word ERASE/DELETE are BAD!!! Of course there are reasons for deletions ... just making a point.

This is not a "patch" issue. Some DB, or other, mechanism either on the SQL Server side or the Access side is removing these records.

This could be caused by a trigger, another store procedure/job that is running somewhere, a third party app cleaning its tables or user intervention.

Could also be the case the records are being "moved/archived" to another table, DB or out of database export target ... ie text file, excel wsheet ...

Need to look a little deeping into this issue. There really are alot of possibilities here.

Thanks

J. Kusch

 

[donutman]

Can you prove that they made it to the SQL box? Or is the only evidence of their existance coming from the front-end app? With VB.NET and its insistance on using disconnected recordset it could easily happen through poor programming. I'm not familar with Access.
Add a delete trigger that logs the date/time.
-Karl

 

[MikeWiz9]

I have seen the records in the SQL Server database table via enterprise manager, so I know they are making it to the server.
One of the ways I'm trying to "monitor" the situation is creating triggers on each table any time a record is deleted and put that into a temporary table. I also have changed the access linked tables using a read-only user account, instead of a reader/writer account.
Of course we don't have sys admin rights, so i cannot run sql trace on the database. So if you have any other suggestions please feel free to suggest them.

 

[donutman]

A bug in the app.
-Karl

 

[SQLBill]

What version and patch level is your SQL Server? Could it be a worm/virus?

If you don't have sysadmin rights on the database, who does? Tell that person what is going on and have them solve it - that's their job. The DBA is responsible for making sure data isn't lost. Since data is being lost the DBA is failing in his/her job and needs to solve this issue. But they can't do it if they don't know about it.

-SQLBill

 

[MikeWiz9]

We're a third party and they give us read/write access. We gave their DBA scripts to create the databases for us. I'm pretty confident it is not our application since we have it installed at other clients and this is the first this has ever happened.
Hopefully we will be able to track down how the records are being deleted. I've added triggers to insert a record into a temp table grabbing username and hostname of the person performing the delete.

 

[SQLBill]

This: I've added triggers to insert a record into a temp table grabbing username and hostname of the person performing the delete: is great except for one thing. If it's happening at the backend, then what is to keep it from happening to the temp tables?

Have you discussed this with the database owners?

I would start doing this....

1. get a retrival of the data currently in the database.
2. enter data
3. go to step 1.

By doing the above you do two things. First one retrieval will show that the data was actually in the database. Then a later retrieval will show that it is gone.

I don't know how big your database is, but this may be the only way to get this solved. You need to prove to the database owner (DBA) that data is missing. Then they need to find out why and get it stopped.

-SQLBill