oracle to sql server
-
Hello everyone. I've got a problem in oracle. I have a oracle database on a OracleLinux OS (Server A),and a Microsoft SQL Server on another server that has Windwos 2008 R2 as OS (Server B). I want to connect to SQL Server via oracle and insert some data to it's tables (from server A to server B). Any suggestions?
-
Hello everyone. I've got a problem in oracle. I have a oracle database on a OracleLinux OS (Server A),and a Microsoft SQL Server on another server that has Windwos 2008 R2 as OS (Server B). I want to connect to SQL Server via oracle and insert some data to it's tables (from server A to server B). Any suggestions?
Where do you want the process to run? How often does it run? Simple solution is to export the data from one server and then import it to the other. You can automate this process with some code. More complex solutions involve specifics of each database. MS SQL Server has tool\system, "SQL Server Integration Service", as part of it which is basically designed for moving data back and forth. One can make this as complex as one wants. MS SQL Server allows one to set up a ODBC connection and use it. So one could do it in SQL Server. Oracle might have something similar but I wouldn't go that route because setting up ODBC on unix variants is not only a problem but you would need to find a MS SQL Server ODBC driver as well. However, Oracle has a java VM built in so you could use a java only MS SQL Server jdbc driver and do a call like that. However if it was me I would stay away from the internal solutions because getting everything to work right via permissions, loading, turning everything on, figuring out ports, etc will take more time than writing the code in the first place. So some solution that uses import\export is going to be less frustrating.
-
Hello everyone. I've got a problem in oracle. I have a oracle database on a OracleLinux OS (Server A),and a Microsoft SQL Server on another server that has Windwos 2008 R2 as OS (Server B). I want to connect to SQL Server via oracle and insert some data to it's tables (from server A to server B). Any suggestions?
Then you need to create a database link. If you want to control it from the SQLServer side you download and install the Oracle client. Configure the client and verify connectivity to the Oracle server using Net Manager. Create a linked server in SSMS using Microsoft OLE DB Provider for Oracle. This is from memory and might be incorrect, but you should have enough info to google the correct procedure. If you want to control the link from the oracle side you have a How-to here[^].
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
Hello everyone. I've got a problem in oracle. I have a oracle database on a OracleLinux OS (Server A),and a Microsoft SQL Server on another server that has Windwos 2008 R2 as OS (Server B). I want to connect to SQL Server via oracle and insert some data to it's tables (from server A to server B). Any suggestions?
you can use ODBC connection
Masoud Nikoofar
-
Hello everyone. I've got a problem in oracle. I have a oracle database on a OracleLinux OS (Server A),and a Microsoft SQL Server on another server that has Windwos 2008 R2 as OS (Server B). I want to connect to SQL Server via oracle and insert some data to it's tables (from server A to server B). Any suggestions?
INSERT INTO tbl SELECT x UNION SELECT y UNION SELECT z isn't valid Oracle not because of the UNION, but because of the SELECTs.
You could simply change it to INSERT INTO tbl SELECT x FROM DUAL UNION SELECT y FROM DUAL UNION SELECT z FROM DUAL
You could quickly do this manually with a search and replace UNION with DUAL UNION and add one DUAL on the end