I'm doing a data conversion from Fox to SQL 2K. The data is a total mess. They are coming from a prior version of fox. One of the tables they sent could not be opened with any ODBC driver, any application like access, DTS would crash when trying to read it. so, I installed Fox 8 from our new MSDN and imported the tables. I can now read them all.
Now, a bigger problem comes to light. The table I could not read has about 730,000 records. My SQL Server DTS package imports the table without error and my exception files show no problems at all. However, There is a difference of about 9,000 records which are not getting imported. I've identified a few and have take a hard look at them but I can't see any reason why. All I can come up with is the idea that there is some sort of corruption going on.
What I ended up doing was a query to get everything from the table and then import that to SQL Server. By doing so I got all the records. That is just crazy. I can also do a query using a linked server to count the rows in fox but it's worthless because it gives the incorrect row count that the import reflects.
What I need is a simple way to get a row count for all tables in the Fox DB. I've tried UNION queries in Fox but it's not working out. Does anyone know of how this can be viewed in Fox or how I can throw a curor together in Fox to get these?
Firstly, Do any of the records that you are trying to process have the deleted mark set to true (are they deleted) ? If they are then I believe that the DTS processor will ignore those records, whereas a VFP SQL statement will not.
As for getting a record count in a VFP table. There are many ways to do this.
1.) Use the RECCOUNT() function. (be aware, it also counts DELETED records).
2.) Write a query to SELECT * and then do " ? _tally " at the command window (without the quotes) It too will count deleted records unless you add ! Deleted() to your WHERE clause.
You will have to do this to each TABLE in your database as each TABLE has its own record count.