I am trying to replicate an archiving process that is not working correctly in a third party software. What I am trying to do is to copy all records prior to a given date and transfer them into a clone of the original data file. Then delete them from the original file.

The partially completed statement that I used is:

INSERT into ContHist_Recs401 ("Conthist Fund","Conthist Member","Conthist Eff-Date","Conthist Type","Conthist Acc Type","Conthist Tran Type","Conthist Batch No","Conthist Pad","Conthist Flags","Conthist Coy Code","Conthist Preserved","Conthist Unpreserved","Conthist Pres Units","Conthist Unp Units","Conthist Date Appl","Conthist Ledger Date","Pad","Conthist Due Date","Conthist Def Unit Fl","Conthist Created","Conthist Modified","Conthist Deleted")
SELECT "Conthist Fund","Conthist Member","Conthist Eff-Date","Conthist Type","Conthist Acc Type","Conthist Tran Type","Conthist Batch No","Conthist Pad","Conthist Flags","Conthist Coy Code","Conthist Preserved","Conthist Unpreserved","Conthist Pres Units","Conthist Unp Units","Conthist Date Appl","Conthist Ledger Date","Pad","Conthist Due Date","Conthist Def Unit Fl","Conthist Created","Conthist Modified","Conthist Deleted"
FROM "Mem Cont Hist Recs"
WHERE "Conthist Ledger Date" <= '2002/09/30'

But this produces the following error:-
ODBC Error: SQLSTATE = S1000, Native error code = -4977
The data buffer parameter is too short(Btrieve Error 22)

Even when I export the records out into a text file and import the text into the cloned data file I get the same response.

The software is Windows 2003 with Pervasive 8 and is run through the Remote Desktop.

Sounds like the table definition is not correct. First thing to do is make sure that the "ContHist_Recs401" and the "Mem Cont Hist Recs" tables pass a database consistency check. If they don't post the failures. If they do pass, then you'll need to compare each field in "ContHist_Recs401" to it's corresponding field in "Mem Cont Hist Recs" to make sure they are exactly the same.

Thanks for the response. I'm not sure of what you mean about the consistency test, what tools are needed for this?? Both data files and fields should be identical as they were cloned off the original file.

Using the Pervasive Control Center, right click on the database name then select "Tasks" then "Check Database". This will bring up the consistency check. Check all of you tables paying especially close attention to the tables in your query (or queries) and the system tables (those that start with X$). If the tables in your query fail with "fixed length mismatch", that's the problem.

Thank you for this information, it has pointed out an anomaly with the original data file supplied by the third party software company. Adding up all the field lengths came up to to 161 whereas the data file was built with a record length of 162.

Hopefully when they fix the record length it will resolve the data buffer error I am getting.

Once again thank you for pointing me in the right direction.