mssql to oracle db integration

[shndmlnd]

Hi

1) how can i do database integration where mssql is situated on a server and the oracle is situated on another server.
mssql db have its own schemas and oracle db has its own...
i want to integrate it.

2) please suggest the solution ,If i take both databases as oracle

thanks,
milind

[rmiao]

What do you mean integration? In sql server, you can create linked server to point to Oracle then query Oracle db via linked server.

[shndmlnd]

Thanks for reply,
1)i have a application which uses mssql as a db.
i want to dump data from mssql database to oracle database which is situated on another server on hourly basis.how can i achive this? and what if both db hv different schemas.then how can i map these two db.
i hv heard about DTS but not sure how to use it.
can u please tell me how can i do it step by step.


2) if i hv oracle on both the servers then how can i transfer that data from one server to another server's db.

[rmiao]

Yes, you can use dts to move data between sql server and Oracle. Need to install Oracle client on sql server. Then create a dts package, choose sql server as source and Oracle as target. You can do column mapping in the package.

[shndmlnd]

Thanks for quick reply...
But if i take oracle on both server then how can i transfer data from one server database to another server database.

and i want to do it on hourly basis.
Please advice.

thanks
Milind

[skhanal]

If both servers are Oracle then you can use Oracle DBLink or use any method to dump out data in a CSV format and use SQLLoader to load it.

[shndmlnd]

Thanks for reply.

but if both databases have different schemas them how can i map them?
is there any easy way out to map these two databases.
cause i want to dump data from one database to another on hourly basis.i want to append only new data to second oracle database not whole database.
how can i achive this?

[rmiao]

You can load data to staging table then process them with sql statements.

[shndmlnd]

Thanks for the Hint.

[stecal]

Oracle Heterogeneous Services
http://download-west.oracle.com/docs...o.htm#sthref15