Post reply

Linked Server SQL 2008 64 bit Oracle

  • April 13, 2011 at 3:51 am

    #238368

    Hello there,

    I'm trying to access my customers Oracle database with a SQL Linked Server. The error I get is:

    Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server...

    I did the following steps:

    1. Installed Oracle client 10g with a custom installation and selected everything.

    2. Created a tnsnames.ora

    dev =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dijkora01)(PORT = 1521))

    (CONNECT_DATA =

    (SERVICE_NAME = dev)

    )

    )

    3. Tested this with tnsping dev, which said that the connection is OK.

    4. Activated 'Allow inprocess' for my OraOLEDB.Oracle provider for the linked servers in my SQL Server Management Studio.

    5. Created a new linked server with:

    Linked Server: IAP

    Provider: Oracle Provider for OLE DB

    Product name: Oracle

    Data source: dev

    In the security tab I activated 'Be made using this security context'. When I now press 'OK' I get the above error message.

    Can someone help me with this?

  • April 14, 2011 at 3:28 pm

    #1311889

    Make sure that you have the right Oracle Client installed.

    I know we had problems because the ORA client was a 32 bit version and the Oracle Instance was an x64.

    try to drop the linked server and recreate it again once you have verified the client

    EXEC master.dbo.sp_addlinkedserver @server = N'MyOracleServer',

    @srvproduct = N'Oracle', @provider = N'OraOLEDB.Oracle',

    @datasrc = N'OracleDB'

    GO

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyOracleServer',

    @useself = N'False', @locallogin = NULL, @rmtuser = N'MyOracleUser',

    @rmtpassword = 'MyOraclePasswod'

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply