Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: Fox Pro 8 Record counts

    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?

  2. #2
    Join Date
    Aug 2004
    Posts
    28

    Cool

    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

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

  4. #4
    Join Date
    Aug 2004
    Posts
    28
    I will work something up for you. It might be tricky tho !


    AmcAmx

  5. #5
    Join Date
    Aug 2004
    Posts
    28

    Smile

    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 *********
    ********************************
    Last edited by amcamx; 08-11-04 at 12:30.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •