What FoxPro data tools can I use to find corrupted data?

Question

I have some SQL Server DTS packages that import data from a FoxPro database. This was working fine until recently. Now the script that imports data from one of the FoxPro tables bombs out about 470,000 records into the import. I'm just pulling the data into a table with nullable varchar fields so I'm thinking it must be a weird/corrupt data problem.

What tools would you use to track down a problem like this?

FYI, this is the error I'm getting:

Data for source column 1 ('field1') is not available. Your provider may require that all Blob columns be rightmost in the source result set.

There should not be any blob columns in this table.

Answer 1

Have you tried writing a small program that just copies the existing data to a new table?

Also,

http://fox.wikis.com/wc.dll?Wiki~TableCorruptionRepairTools~VFP

Answer 2

Cmrepair is an excellent freeware utility to repair corrupted .DBF files.

Answer 3

My company uses Foxpro to store quite a bit of data... In my experience, data corruption is very obvious, with the table failing to open in the first place. Do you have a copy of foxpro to open the table with?

Answer 4

Thanks for the suggestions. I don't know if it a corruption problem for sure. I just started downloading FoxPro from my MSDN Subscription, so I'll see if I can open the table. SSRS opens the table, it just chokes before running through all the records. I'm just trying to figure out which record it's having a problem with.

Answer 5

@Lance:

if you have access to Visual FoxPro command line window, type:

SET TABLEVALIDATE 11
USE "YourTable" EXCLUSIVE    && If the table is damaged VFP must display an error here
PACK    && To reindex the table and deleted "marked" records
PACK MEMO    && If you have memo fields

After doing that, the structure of the table must ve valid, if you want to see fields with invalid data, you can try:

SELECT * FROM YourTable WHERE EMPTY(YourField)   && All records with YourField empty
SELECT * FROM YourTable WHERE LEN(YourMemoField) > 200   && All records with a long memo field, there can be corrupted data

etc.

Answer 6

At 470,000 records you might want to check to see if you're approaching the 2 gigabyte limit on FoxPro table size. As I understand it, the records can still be there, but become inaccessible after the 2 gig point.