PDA

View Full Version : Fox Pro 8 Record counts

DBA-ONE
08-05-04, 12:16
Greetings,

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?
amcamx
08-10-04, 11:00
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.

Hope this helps !

AmcAmx
DBA-ONE
08-10-04, 11:03
I was actually looking for something like a stored procedure that wouls look at user tables, show me the name and count like:

table_1 20
table_2 21

I'll check out the deleted stuff you mentioned. This seems more than likely.
amcamx
08-10-04, 12:11
I will work something up for you. It might be tricky tho !


AmcAmx
amcamx
08-10-04, 16:14
OK.. here you go...try this one !

It will display a message box with a list of ALL the tables in your database.

Please feel free to change it to suit your needs !

oops.. forgot to tell you. When you run it it will ask for the database that you want to get the rocrod counts from. Go find the Database, select it and you will get your results.


AmcAmx


********************************
******** START OF PROGRAM ********
********************************
CLOSE DATABASES ALL
MessageText = ""

File2Use = GETFILE("DBC", "Choose a Database", "Run")

* Get all the records from the selected database that are TABLE records.
SELECT * ;
FROM (File2Use) ;
WHERE ALLTRIM(UPPER(ObjectType)) = "TABLE" ;
INTO CURSOR MasterTable

* Close the DBC.. We do nto need it anymore.
USE IN (JUSTSTEM(File2Use))

* Are there actually any TABLE records in this Database ?
IF _TALLY > 0

SCAN

* STORE the table name TO a variable
TableName = ALLTRIM(ObjectName)

* Select all the records from the requested table except for the deleted ones.
SELECT * ;
FROM (ADDBS(JUSTPATH(File2Use)) + TableName) ;
WHERE ! DELETED() ;
INTO CURSOR Junk

* Store the curretn table info into the message string.
MessageText = MessageText + SPACE(5) + TableName + PADL(ALLTRIM(STR(_TALLY)), 8, " ") + CHR(13)

* Close the tables we just opened.
USE IN Junk
USE IN (TableName)

ENDSCAN

* Display the message to the users.
MESSAGEBOX(MessageText, 0 + 16 + 0)

ELSE
* Display a message indicating that there are not any TABLES in this DATABASE.
MESSAGEBOX(" There are no tables available in this database.", 0 + 16 + 0)
ENDIF

* Close ALL the data tables.
CLOSE DATABASES ALL

RETURN
********************************
********* END OF PROGRAM *********
********************************