February 12, 2016 at 9:53 am
Can i connect SQL Server 2012 with oracle and use data from oracle table inside SQL server?
Thank you
February 12, 2016 at 12:07 pm
Should be possible using OPENQUERY.
Documentation: https://msdn.microsoft.com/en-us/library/ms188427.aspx.
February 12, 2016 at 12:49 pm
You can do this using a linked server. I believe the only requirement is to load the Oracle driver on the server. And like the previous post use OPENQUERY. We do this in our environment quite a bit. This link will be useful: https://support.microsoft.com/en-us/kb/280106
February 12, 2016 at 1:17 pm
Ok.. so is it need to be installed on server? if i dont have access to server, i can't connect SQL server and Oracle?
February 12, 2016 at 1:24 pm
No need to install any additional components.
Creating a linked server and using openquery are standard features in SQL Server.
(You do obviously need access to SQL Server, and you need credentials for accessing Oracle)
February 12, 2016 at 2:37 pm
is there any alternative of linked server? we no longer support linked server..everything is being done by SSIS packages
February 12, 2016 at 3:11 pm
I have limited SSIS experience, but I do know that it comes with Oracle connectors. So you should not have any issue connecting to Oracle, getting data out, and combining it with the SQL Server based data sources in your data flow.
Perhaps this can help you further: http://stackoverflow.com/questions/18096409/connecting-to-oracle-database-using-sql-server-integration-services
February 12, 2016 at 3:19 pm
SSIS is an ETL tool, so you would use it to copy data to and from Oracle. If what you want is to query data from Oracle, then you'll need a linked server or openquery, or openrowset.
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
https://support.microsoft.com/en-us/kb/280106
You can attempt using whatever client provider is already installed on your SQL Server machine, but if a client is needed for install, then you can use the Oracle Instant Client, which suits purpose for connectivity. There is no need to install the full Oracle client software.
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 12, 2016 at 7:28 pm
I've done it successfully with a linked server. I installed the Oracle client on the server. Also, I was advised to use the Oracle driver for connectivity instead of the Microsoft driver because the Oracle driver does a better job of passing the WHERE clause remotely and returning the set. I guess there are times when the Microsoft driver will pull back the whole query and then process the WHERE clause locally. This would have been completely impractical for my application because of the number of rows in the remote database.
You do have to have your tnsnames.ora file defined properly. The Oracle driver uses Oracle networking.
I've never gotten it to work other than using a linked server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply