SQL 2008 R2 linked server to 11g

  • 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?

  • 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.

  • 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.
  • 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.

  • 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.
  • 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.

  • 32-bit software (SSMS, BIDS, etc) requires 32-bit Oracle Client. 64-bit software (SQL Server Database Engine)requires 64-bit Oracle client.

  • 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

  • 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