Thanks Naomi. I checked out BCP and it will definitely do the trick. SSIS I haven't worked with and would probably be a learning curve?
However, for both of these, it looks like there would be permissions issues and issues with having BCP installed/accessible? This needs to work for non-administrative regular users. And regarding SSIS, I need this to be launched from a VFP program, not a scheduled job.

In the meantime though, I tried:
select * from curactivity where 1=2 into tblactivity_vfp
select curactivity
copy to array a_curactivity
select tblactivity_vfp
append from array a_curactivity
release a_curactivity

This works much faster for the copy to part - especially if set talk is off and the status bar is off, or odometer is set to a high number. The machine I am using it on has 4 gigs of RAM. If I don't "release a_curactivity", and do it multiple times I start to get out of memory errors. It might be helpful if I could calculate the memory requirements in advance, and break it up into pieces if I had to. How can I calculate the memory requirements?

For the sqlexec part, I am using an odbc connection string. For some reason OLE DB is not working on the machine. How much faster would OLE DB be?

Also, thanks to those who suggested reporting solutions where we don't have to retrieve the data into a dbf. However, I think I should have been more clear. The need to get the data into a dbf is not just for reporting. There is a application with lots of functionality, already built, that needs the dbf's. It's not a great design, but it's what we have to work with, for now.






>>I have a program that gets data from sql server and puts it into vfp tables (free tables). From there reporting is done against the dbfs. This needs to be done multiple times a day. One table, tblactivity, has about 500K records.
>>
>>Regarding the following code:
>>lnr=sqlexec(lnh,"select * from tblactivity","curactivity") && takes 30 or 40 seconds
>>copy to tblactivity_vfp && takes a few minutes?? haven't benchmarked exactly, but it is slow
>>
>>Is there a way to speed this up? Years ago I used BCP, don't remember much about it right now, but thinking it would speed up writing to a text file, but then I'd have to append into a dbf anyway.
>>
>>Is there any way to bypass sending results to a cursor first, a way to use sqlexec to go straight to a dbf?
>>Any way to speed up the copy to?
>>
>>Thanks.
>
>BCP is the way to go or SSIS package scheduled as a job to run.
>
>I suggest to look into second alternative.