Re: data transfer from oracle 7.3 -> MS Sql Svr 6.5 (nightly 'snapshots')

Tom Stamm wrote in message <348C1528.93ADECEB_at_bah.com>...
>Is there a clean way to keep a sql server database synched with an
>oracle database?

 <snip>
>So far it looks like having the oracle database dump the data to a set
>of text files, and then having the box with SQL server grab those files
>via ftp, and load it into SQL Server with bcp is the easiest option
>(without buying some expensive data-management tools, anyway).

It also sounds like the best solution to me. The complete transfer can also be scripted and scheduled for an automated run. The only problem with this method will be the data volumes - if this grow too large then you will need to apply changes only to SQL-Server instead of replacing all the data.

>An ODBC connection can be established between the two, so I was
>wondering if there's some other, more direct, solution...?

Hmm.. I think there are two other types of solutions. You can use a database gateway like Oracle's Transperant Gateway for SQL-Server. This makes the SQL-Server database available as an "Oracle" database via SQL*Net. How fast, robust and flexible this is I have no idea. You can probably setup SQL-Server as a database link in the source Oracle database and push data across...

The 2nd solution will be to write a client application that performs the data transfer. This may be a better solution, if the data volumes are low and/or transaction overheads are minimal. It should be possible to bypass/decrease transaction processing for large volumes of data, but this will require using native DB-Library by the client app to talk to SQL-Server. DB-Library has BCP (bulk copy) API calls. You should be able to run a SELECT on Oracle and pass this data into the BCP api call via some kind of data stream.

But IMHO your current solution is probably easier and faster - even if "primitive". :-)

regards,
Billy Received on Tue Dec 09 1997 - 00:00:00 CST