Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 R2 linked server to 11g Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 1:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
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?
Post #1367248
Posted Tuesday, October 2, 2012 2:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:22 AM
Points: 518, Visits: 260
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.
Post #1367293
Posted Tuesday, October 2, 2012 6:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 1,381, Visits: 2,922
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
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1367359
Posted Thursday, October 4, 2012 12:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 1,402, Visits: 6,891
Check out the Driver mania thread in this forum
Post #1368163
Posted Thursday, October 4, 2012 10:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
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.
Post #1368576
Posted Thursday, October 4, 2012 1:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1368658
Posted Thursday, October 4, 2012 2:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:14 AM
Points: 65, Visits: 404
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.
Post #1368694
Posted Friday, October 5, 2012 12:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:22 AM
Points: 518, Visits: 260
32-bit software (SSMS, BIDS, etc) requires 32-bit Oracle Client. 64-bit software (SQL Server Database Engine)requires 64-bit Oracle client.
Post #1368841
Posted Tuesday, January 15, 2013 12:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:32 AM
Points: 277, Visits: 290
I have tried this one
http://queryingsql.blogspot.in/2012/10/linked-server-setup-with-oracle.html?utm_source=BP_recent


Thanks.......
-----------------------------------
My Blog | Articles
Post #1407057
Posted Wednesday, April 3, 2013 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:52 AM
Points: 187, Visits: 386
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



Post #1438387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse