Web Development

Ask something
Discussion Locked

Oracle SQLServer

0 helpful
5 replies

by agg_bcn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I need to exchange data from a server (let's say Server A) which runs an Oracle database to another server (Server B) which runs SQL Server. I'm thinking about using XML to do that, but I have no idea how I should do it.

I'd like to develop some kind of authomatic mechanisim to read from SQL Server and write into Oracle and then read from Oracle and write into SQL Server.

Any idea?

Thanks in advance.

Alex.

5 replies

by Member 30634329

You may want to try SQL Server's Data Transformation Services (DTS). If you install the Oracle Client on your SQL Server, you will be able to use create connections in DTS to both systems and transfer data between them. Once you have your DTS tasks built and tested, you can schedule them to run automatically on the SQL Server.

by mad-h

Yup, that's the thing to use, there's also a lot you can do in it if you need to manipulate or filter the data during the transfer, using the scripts in the transformations. Not often you'll hear me saying good things about MicroSmurf products, but this is one of them - apparently some companies buy SQL server just for the DTS funtion and don't actually use it as a DB. One thing to be wary of (hey, it is by MS, so there's bound to be some annoying quirks) - if you change the data source, it will eat any transformations running from that source - I discovered this taking data from a file (it even did it if I changed the filename to a file with an identical structure) so I don't know whether it will behave itself doing that with DB tables.

by psinger1

A product know as the Oracle Transparent Gateway. Which is something I've used only once. I've also used DTS twice, for loading into SQL Server. I agree that it is much more user friendly than anything Oracle offers - but I am scared at the thought of using it to update Oracle tables.

by realvdude

If you have clients for both servers, you could also use ODBC for data exchange. The problem here may be in variable types though. ODBC has several levels of standardization, so you can exchange data, but you may not have the same level of information between data structures which would allow you to more closely model your table variables.

by anirbank2000

You can use a java pgm, which can be scheduled to run automatically, or which can run continuously and poll one of the databases.
The pgm will read from Oracle and update into SQL Server, using two connection objects.
Thanks

This discussion is closed to new replies.