Wiki Home

Set Tablevalidate


Namespace: Wiki
This is a new SET command delivered with VFP8. It was conjectured on UT that the command was put into VFP8 as a remedy for MSKB problem Q293638 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293638 and indeed that problem has now been revised to "FIXED" in VFP8! However, it appears that this new command is not related to that fix.

The command's operating result is, to me, not very intuitive at all though it does make sense once one knows < s >.

Some time back in VFP history (version 5 is conjectured, though I think later myself) VFP was changed so as to not error ('Not a table' message I think) when the header count did not synch with the physical file size. I believe that this was not published in any regularly available list of release changes. Certainly at version 5 of VFP, problem reports were describing loss of data during TRANSACTION processing and acknowledged by Q293638 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293638.

SET TABLEVALIDATE optionally performs up to two (2) types of "integrity checks" on (non-temporary) tables: The default setting is 3.
The error condition is numbered 2091 in both cases and the message states: "Table "name" has become corrupted. The table will need to be repaired before using again.".

Here are the intricacies as I have observed them (please add/amend as needed):

Table OPEN time SET TABLEVALIDATE TO 1 or SET TABLEVALIDATE TO 3
Assuming that the table header is lockable and a discrepancy of the record count versus the physical size is encountered, error number 2091 occurs immediately. Any active error routine will, of course, process for it.

Note that a "Waiting for lock" message can be issued and that obtaining the lock on the header is mandatory for further processing to proceed.

Data WRITE (INSERT or APPEND) time SET TABLEVALIDATE TO 2 (or 3, but error should occur on OPEN in that case)
The data to be written is written, then the integrity check is performed. If a discrepancy is found, then the operation is dependent on the buffering in effect: In my testing, neither ON ERROR nor TRY...CATCH...FINALLY intercept the error when any buffering is in effect.

I have also observed oddities in the value of the record count in the table header depending on what setting was in effect and what I/O kind (buffered or not, transactions or not, etc) is used. If I can make some sense out of them I will report here.

NOTE re MSKB problem # Q293638 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293638
The following is the error message, emitted (when there is a problem) on the END TRANSACTION statement execution regardless of the setting for TABLEVALIDATE:
"Table "table" has a file length / record count inconsistency that will not allow the transaction to be committed. Please open the table exclusively and PACK it.".
Note that in this case NO records are written to the table prior to emitting the error.
- I cannot locate a number for this error message (I did not find it in the VFP8 Help of error messages). The error number is 2065. It is NOT documented in the VFP8 Help.

Help! I have just started getting this error within a VFP8 COM server. It is actually a remote view to SQL Server:
"Table "C:\DOCUME~1\ogseutil\LOCALS~1\Temp\C9MG00FZ.TMP" has a file length / record count inconsistency that will not allow this transaction to be committed. Please open the table exclusively and PACK it."
This COM server is the same logic/code that is compiled in an EXE and I have NEVER got this error there. (In other words, its the same nTier business objects being called, regardless of EXE or DLL.
The error # is 2065 as stated above. This is a very mission critical app and I need to get this resolved, but I'm not sure what to do. The error occurs after 3 or 4 updates on the same business object. Doesn't seem to be any logical reason for it. The data does not update. Correction - the data DOES update and the transaction looks normal in SQL profiler -- Randy Jean

Randy, I would suspect the hard drive. On UT another person reported a sudden occurrance of the error and it persisted until he switched HDs for his data. His was not temp data but I doubt that matters. Good luck -- Jim Nelson
PS This topic really should be updated in view of changes in VFP8 SP1.

Thanks Jim. I had thought of that and had our hardware guy check the drive out yesterday and could not find anything wrong with it. So I tried running the EXE version of my app (with the UI) on the same server that is running the COM server DLL and I could not get the error to occur. Again, the only difference is one is compiled as a DLL and the other an EXE, but the business/data tier logic is the EXACT same. So, I decided to try compiling the COM DLL in VFP9 public beta - guess what? No errors! Freakin amazing. Not sure if this makes any difference, but the app that interops with the VFP COM object is a C# program and runs as a Windows service. Also, this is on a Windows 2003 Server -- Randy Jean

Just like Poltergeist: It's back....: After updating runtime DLL's to VFP9 SP1 we're seeing this error again in the app mention above. Will update if I find a fix/workaround. Same code/compiled DLL that was working in VFP9 pre SP1 -- Randy Jean

Hi randy, I've got the same problem in one of my products (VFP8SP1). The error also refers to a temp table (a cursor or view). The problem occurs however in a normal EXE on an END TRANSACTION, though TABLEVALIDATE was set to 0. I hope I can find out the problem. I might want to upgrade to VFP9, but who knows what problems show up then. - Walter Meester

Hi Walter. Sorry to hear you're having the same problem. Of course, your mileage may vary, but in my case, compiling/deploying under VFP9 solved this (and a few other issues) for me. In fact, the app (EXE & COM server) have been in production for about a month now and is more stable than any other VFP app we have deployed under earlier runtime versions. -- Randy Jean

contributors: Jim Nelson, Randy Jean
Category VFP Commands, Category Needs Refactoring
See Also: More 2065 Errors With VFP 9
( Topic last updated: 2005.12.19 02:43:35 PM )