DTS import FoxPro DBF Deleted Records

[stevebanks]

Hi i am trying to import around 30 FoxPro tables into our MS SQL server using DTS import/export wizard. I can select the FoxPro tables using OLE DB Provider for VFP and can actually import the tables into the server ok, however, it imports all rows marked as deleted in the DBF's, i have tried setting the DELETED property to only import non-deleted rows, but the Import/Export wizard seems to ignore it. I cannot pack the VFP tables before i import as they are being used constantly so i cannot get exclusive access.

I have had a look around but i cannot seem to find a solution... has anyone got any ideas as my head is starting to hurt!!

thanks

Steve

 

[AlexCuse]

If you can't clear out the tables, is there a way you can at least identify the rows marked "deleted" and then delete them on the SQL Server side? I don't really know how fox pro handles these "deleted" records, but maybe you could add a case statement returning a bit value based on this property to your source query? Then just delete based on that column contents?

If this doesn't work, can you explain better how this "deleted" property works? I take it these rows are fully ignored by foxpro, and perhaps unqueryable? If thats the case, perhaps just using a query off of the table as your source, rather than the physical table, would be enough?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[stevebanks]

unfortunately the tables that i am trying to import are part of our mail order software, i can't alter the format of these tables without potentially causing problems. We are trying to report on these DBF's but our reports link through ms access and the size of them is now too much for access to handle.

FoxPro handles deleted rows by placing a marker against them, and, until you "PACK" a table these deleted rows are still there but should not show by default. Unfortunately the import/export wizard is seemingly ignoring the default and even when over written is ignoring the command, unless i am missing something?

 

[AlexCuse]

This is a tough one. If anyone would know how, I'd think Darren would

Couple ideas though (I'd try them in this order, as they get progressively hack-ish):

1. Have you tried using an ODBC foxpro driver?
2. If you can link to these tables in Access, can you do your import from the Access linked table?
3. Can you export a file from fox and have SQL Server pick it up somewhere?

Please post back what you find, as I am looking at needing to interface with a FoxPro app sometime soon, and will surely run into this type of problem!

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[stevebanks]

hi alex,

i have managed to sort this, although it is as you sasy, a little hack-ish!

we have around 40 free table DBF's, and when importing directly using the DTS wizard it imports the deleted records.

the work around i found was:

1. Create a Foxpro database DBC file
2. Add the free tables as part of this database
3. Create Views of all tables which are just SELECT * type views.
4. Open the DTS wizard and connect to the DBC file
5. Import the views rather than the tables.

This appears to have gotten around both the need to pre-PACK the DBF files and exclusivity problems. Obviously this can then be automated (which is my next step!)

Hope this can be of help if you get a problem with your future development!



Steve

 

[AlexCuse]

Thanks steve, good call on using FoxPro views instead of access! It would go ahead of #2 on my scale of hack-ness, we'll call it 1a.

I appreciate your taking the time to check back.



[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[stevebanks]

no problem...

a word of caution for those who do think that access is the best way via odbc, we have steered clear of this recently because the more complex your query, the data "jumps" around, i think this is something to do with the odbc driver not being up to it, the exclusivity on the foxpro tables and the amount of data.

steve