Web Development

General discussion


Oracle SQLServer

By agg_bcn ·

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.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Server DTS

by billbohlen@hallmarkchannl In reply to Oracle SQLServer

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.

Collapse -

SQL Server DTS

by Mad-H In reply to SQL Server DTS

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.

Collapse -

The official Oracle Solution is ....

by psinger1 In reply to Oracle SQLServer

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.

Collapse -


by Realvdude In reply to Oracle SQLServer

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.

Collapse -

Use a java pgm

by anirbank2000 In reply to Oracle SQLServer

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.

Related Discussions

Related Forums