locked
Setting Up An Oracle Linked Server RRS feed

  • Question

  • Hi, I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
    I have managed to set up a Oracle ODBC Driver on the server which when I click Test Coonnection tells me "Connection successful" so I beleive this is OK
    The following script is what I have done to set up the link server
     
    /****** Object:  LinkedServer [ORACLE_TEST]    Script Date: 09/15/2011 15:43:35 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'ORACLE_TEST', @srvproduct=N'ORACLE', @provider=N'MSDASQL', @datasrc=N'SYSTEM_DNS_DATA_SOURCE_NAME'
     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_TEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'SYSTEM_DNS_DATA_SOURCE_USER_NAME',@rmtpassword='SYSTEM_DNS_DATA_SOURCE_PASSWORD'
    
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'rpc out', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'use remote collation', @optvalue=N'true'
    

    When I click Test connection I receive the following error:
     
    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ORACLE_TEST".
    OLE DB provider "MSDASQL" for linked server "ORACLE_TEST" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
    OLE DB provider "MSDASQL" for linked server "ORACLE_TEST" returned message "[Oracle][ODBC][Ora]ORA-28547: connection to server failed, probable Oracle Net admin error
    ". (.Net SqlClient Data Provider)

     Any ideas to help solve please!?
     

    Thursday, September 15, 2011 3:02 PM

Answers

  • Hi jameslester78,

    Could you please check that you have used the data source field with the same as Oracle Net Manager Service Naming tree?

    Please take a look at the following thread with the same issue:
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1562c89a-c0c9-4fc7-9aed-9ee3b6516a54


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Stephanie Lv Friday, September 23, 2011 12:07 PM
    • Marked as answer by Stephanie Lv Monday, September 26, 2011 1:07 PM
    Tuesday, September 20, 2011 12:20 PM

All replies

  • James,

     

    try this:

     

    EXEC sp_addlinkedserver

    @server = 'name',

    @srvproduct = 'Oracle',

    @provider = 'OraOLEDB.Oracle',

    @datasrc = '(DESCRIPTION=ADDRESS = (PROTOCOL = TCP)(HOST = Host)(PORT = Post))(CONNECT_DATA=(SID=SID)))'

    GO

    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = 'Name',

    @useself = 'FALSE',

    @rmtuser = 'User',

    @rmtpassword = 'Password'

    GO

     

     

    The information of datastc are on tnsnames.ora


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    Thursday, September 15, 2011 7:18 PM
  • I get a different error message

     

    The OLE DB provider "OraOLEDB.Oracle" has not been registered. (.Net SqlClient Data Provider)

     

    I am have instantclient installed and trying to connect via odbc.

    What do I need to install to make your method work?


    *EDIT*

    I installed Oracle Data Access Components and now I get this error

     

    Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TESTORACLE2".
    OLE DB provider "OraOLEDB.Oracle" for linked server "TESTORACLE2" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (.Net SqlClient Data Provider)

     

    Any ideas?

    Monday, September 19, 2011 8:17 AM
  • Hi jameslester78,

    Could you please check that you have used the data source field with the same as Oracle Net Manager Service Naming tree?

    Please take a look at the following thread with the same issue:
    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1562c89a-c0c9-4fc7-9aed-9ee3b6516a54


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Proposed as answer by Stephanie Lv Friday, September 23, 2011 12:07 PM
    • Marked as answer by Stephanie Lv Monday, September 26, 2011 1:07 PM
    Tuesday, September 20, 2011 12:20 PM
  • James,

     

    try this:

     

    EXEC sp_addlinkedserver

    @server = 'name',

    @srvproduct = 'Oracle',

    @provider = 'OraOLEDB.Oracle',

    @datasrc = '(DESCRIPTION=ADDRESS = (PROTOCOL = TCP)(HOST = Host)(PORT = Post))(CONNECT_DATA=(SID=SID)))'

    GO

    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = 'Name',

    @useself = 'FALSE',

    @rmtuser = 'User',

    @rmtpassword = 'Password'

    GO

     

     

    The information of datastc are on tnsnames.ora


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    Thanks, this resolved my issue. Kind Regards

    Wednesday, June 3, 2015 1:13 PM