I have a database on a MSSQL Server 7 running on a Windows NT machine. This database works the last 4years with no problem. The database file is now 3 GB. The last two months every single day an update error on a specific Table occurs. I cannot update a record in this table, and after this error occurs more than 100 records or more seemed to be locked. While I cannot edit the previous records I can create a new one with no problem. I have this problem once every sigle working day (ant the currious thing is that is occured from 8 to 10 in the morning every singke working day) . The table seems to work again after some hours or after one single day, or after I stop and start again the sql server service. Some times I have to wait some hours in order to manage to edit records in this table again. (I edit the records by using a program created with Microsoft Access and ODBC. When the error occurs I cannot edit the records neither by Access not by SQL Server Query Analyser).
This error doesn't occur when entering a new record, and might occur when only one user works with the specific Table. This table has 75 fields and about 70000 records.
Similar problems not in this degree exist on a second database installation with about 25 users with the same table but running under a Windows 2000 Server machine.
Does the error occur when you try to edit the records using ONLY query analyzer?
Are you using an MS Access mdb file to edit the data, or are you using a Microsoft Access Data Project (ADP) file?
The error occurs both with Access (I'm using an mdb file connected with ODBC with SQL Server) and with the Query Analyser. When this error occurs I cannot edit this table from any computer in the network.
Therefore I'm trying to edit the table using ODBC. I haven't tried by using an ADP project, or by using another program like Delphi which uses another connection method like OLE DB.
when you get to the point when "this" error occurrs again, can you switch to query analyzer and write an update query for a specific record?
after you get "that" error in query analyzer, paste it here. i suspect you can also get this error from sql server errorlog by double-clicking on the entry with the error. i suspect the error you're getting is referencing the total record length. if this is the case you'll need to either shrink a couple of fields or split the table into two to allow for all your fields to be updateable. to do that seemlessly for the front-end you'll have to write a view and reference this view in your inserts, updates, deletes, and selects.