Running a query on both SQL Server and Oracle databases
How do I run a query that queries a SQL Server database and an Oracle server database, on two different servers, and uses all the data?
Software/Hardware used:
Software/Hardware used:
ASKED:
March 3, 2009 7:47 PM
UPDATED:
March 3, 2009 8:39 PM
Answer Wiki:
If you are going to run the query from the Oracle database, you would need to use <a href="http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10764/intro.htm">Heterogeneous Services</a> and create a database link to the Sql server database.
Then you can access Sql Server's tables by suffixing them with '@<database_link_name>'
If you are going to do it from Sql Server, you will need a <a href="http://decipherinfosys.wordpress.com/2007/07/16/linked-servers-in-sql-server/">linked server</a>.
Have a look at these links:
<a href="http://support.microsoft.com/kb/280106">How to set up and troubleshoot a linked server to an Oracle database in SQL Server</a>
<a href="http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html">Setting Up an Oracle Linked Server</a>
<a href="http://www.dbasupport.com/oracle/ora10g/connection01.shtml">Making a Connection from Oracle to SQL Server</a>
<a href="http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html">Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)</a>
<a href="http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm">Database link from Oracle to SQL Server</a>
To see all answers submitted to the Answer Wiki: View Answer History.
Need more help using SQL/Oracle queries? Here’s some online practices to improve your abilities.