SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 R2 linked server to 11g


SQL 2008 R2 linked server to 11g

Author
Message
scogeb
scogeb
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 Visits: 420
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?
SQL Surfer '66
SQL Surfer '66
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 328
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.
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3487 Visits: 3401
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.
Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7398 Visits: 10024
Check out the Driver mania thread in this forum
scogeb
scogeb
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 Visits: 420
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14603 Visits: 4639
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.
scogeb
scogeb
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 Visits: 420
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.
SQL Surfer '66
SQL Surfer '66
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 328
32-bit software (SSMS, BIDS, etc) requires 32-bit Oracle Client. 64-bit software (SQL Server Database Engine)requires 64-bit Oracle client.
UmaShankar Patel
UmaShankar Patel
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 339
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
John Langston
John Langston
SSC-Addicted
SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)SSC-Addicted (490 reputation)

Group: General Forum Members
Points: 490 Visits: 525
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search