I also noticed that the VFP performance was pathetic. I think it is because each VFPOLEDB write is in it's own "transaction" where is has to allocate disk space, do error checking, etc. DTS just wouldn't cut it with OLEDB, I didn't try the ODBC driver though.

What I ended up doing, which I really did not want to do, was create a mini VFP .exe with the VFPCOM utility. Then I used the RStoCursor method in it to get a VFP cursor, which I then copied to my local DBC, then I did a query on the InfoSchema.Columns table in MSSQL for what the column names were really supposed to be and renamed each VFP column properly.

So what I was doing with ADO and the OLEDB driver used to take about 40 minutes for the million-row table, and about 90 minutes for the 3.5-million-row table. Now it takes 4 minutes on the small one and 15 minutes on the big one.

My knowledge of ADO is not all that great - I have yet to see an advanced book out there with detailed howto's and explanations - but it seems like I should have been able to do this with the driver. When pulling data down, it always took around 30 seconds to get the million-row ADO recordset from MSSQL, and then 39.5 minutes to save them - all in what amounted to 90mb of data.




>Are you trying to do it thru a DTS package? I did some testing recently and the performance of a SQL-to-VFP transfer was pathetic. The other way 'round - VFP-to-SQL - is excellent.
>
>Believe it or not, I got significantly better results using a VFP remote view to suck the data out, then write it to a local table. For a 100K record table duration was measured in seconds, not minutes.