October 2, 2012 at 1:05 pm
I've been tasked with setting up an Oracle linked server on a new SQL 2008 R2 install. I have installed the Oracle win64 11gr2 client on the windows 2008 r2 server. I then used Oracle Net Manager to create Service Naming for the connection I want. Do I then use that Service Naming for my linked server in SSMS? It looks like that is what was done on the old server I'm replacing but I didn't set that one up. Is this the most effecient way to do it?
October 2, 2012 at 2:46 pm
I haven't worked with Oracle 11, but this is the procedure I've used with previous Oracle versions. The little I saw of Oracle 11 before I left my previous job tells me that it still works the same way.
October 2, 2012 at 6:40 pm
This may help, it's relevant for 2k8r2 also:
http://www.expressnetsolutions.com/sqldch/?p=89
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 4, 2012 at 12:00 am
October 4, 2012 at 10:45 am
Thanks for the links guys! Wow, what a zoo. I finally got it to work, but it was a chore. I had to install both the 32 and 64 bit clients. I then used Oracle Net Manager to create a service name in both the 32 and 64 bit clients. Then in SSMS, I created the linked server. I was then able to run openquery commands successfully.
October 4, 2012 at 1:09 pm
scogeb (10/4/2012)
Thanks for the links guys! Wow, what a zoo. I finally got it to work, but it was a chore. I had to install both the 32 and 64 bit clients. I then used Oracle Net Manager to create a service name in both the 32 and 64 bit clients. Then in SSMS, I created the linked server. I was then able to run openquery commands successfully.
I'm curious about why both the 32 and 64 bits clients had to be installed, after all I expect the connection to be established by a single client as opposed to a colaborative effort between two different ones.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 4, 2012 at 2:08 pm
I'm curious as well, LOL! I installed the 64 bit first, so maybe I only needed the 32 bit client for it to work. I do know for certain, it didn't work with just the 64 bit client. On the next server I set up, I will try the 32 bit client by itself first. I'll post back after I try that, but it might be awhile.
October 5, 2012 at 12:24 am
32-bit software (SSMS, BIDS, etc) requires 32-bit Oracle Client. 64-bit software (SQL Server Database Engine)requires 64-bit Oracle client.
January 15, 2013 at 12:46 am
I have tried this one
http://queryingsql.blogspot.in/2012/10/linked-server-setup-with-oracle.html?utm_source=BP_recent
Thanks.......
-----------------------------------
My Blog[/url] | Articles
April 3, 2013 at 8:35 am
I spent quite a bit of time on this. I found BOL to be less than useful (dated information). I never a number of posts that indicated installing both 32-bit and 64-bit Oracle clients
I am listing my steps in the hope that someone might get some benefit from them.
Both of my database servers (SQL 2008 R2 and Oracle 11.2.0.1) are Windows 2008 R2 64-bit.
1) From the Oracle website download section, I down loaded from under the oracle database section the win64_11gR2_client and installed it on the SQL Server database server. I rebooted here (might have needed to do so).
2) I used Oracle Net Manger to configure the client. When tested I successfully connected to the target Oracle server.
3) Added a tnsnames.ora file (point to the Oracle target) in C:\oracle\product\11.2.0\client_1etwork\admin
4) Defined the Linked Server - I mapped specific logins; checked the Allow inprocess box under the OraOLEDB.Oracle Provider
Querying from the SQL Server server to the Oracle server was successful
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply