Greetings,

I have a newbie question. I've been reading methods of syncing a Foxpro
DB to a MSSQL server daily. Each record luckily contains a last
modified date so it may make it easier.

DTS seems like the way to go but I wish to perform more advanced logic
mainly:

* Sync only new records
* Sync only modified records (drop old row, input new row)
* Dropping the entire table is not an option since it's too large to
re-copy each time.

Achieving the above two requires me to write some scripts to drop any
old records. What ingredients do I need to look at?

Would I use DTSLOOKUP to say query the last modified record in my MSSQL
DB? Would I need to readup on ActiveX or is there a less overkill method?

Would I require some stored procedure to drop old records and copy new
ones?


Thanks!

How about you create from SQL Server a linked Server of the Foxpro DB.

For new rows you compare Keys and insert where the ID does not exist in
SQL Server.
For Updates you compare keys, where they match you can then do an update
on the other attributes (the advantage here is that you will not break
RI as readily as dropping the row and recreating)
For removing (deletes) you can compare keys and where the key exists in
SQL Server but not Foxpro then delete.

In the insert and the delete if you have RI in place then the inserts
will have to follow in Parent --> Child order for the inserts and Child
--> Parent order for the deletes.

Allan


"Yorro" <yuniverse (AT) hotmail (DOT) com> wrote

Great stuff Allen. I never covered Linked Servers and I'm reading it up
now. Looks like it'll do the job.

Thanks.


Allan Mitchell wrote: