Click here to monitor SSC
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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1840 Visits: 3382
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
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: 1588 Visits: 9716
Check out the Driver mania thread in this forum
scogeb
scogeb
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 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 Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 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 Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 337
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 Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 514
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