locked
How to create Link Server to connect Oracle database? RRS feed

  • Question

  • How connect Oracle Database?

    Software Specification As Below

    Oracle Version:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    PL/SQL Release 12.1.0.2.0 - Production
    "CORE 12.1.0.2.0 Production"
    TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
    NLSRTL Version 12.1.0.2.0 - Production

    SQL Server:

    Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86) 
    Feb 20 2014 19:20:46 
    Copyright (c) Microsoft Corporation
    Express Edition on Windows NT 6.3 <X64> (Build 17134: ) (WOW64)

    Operating System:

    Window 10  64bit 

     https://stackoverflow.com/questions/47205883/how-to-create-link-server-to-connect-oracle-database


    Sunday, September 2, 2018 4:15 PM

Answers

  • See

    https://logicalread.com/sql-server-2014-and-oracle-12c-linked-servers-mo01/#.W4wRGM4zapo

    http://www.recurringtheme.com/?p=3617


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Sunday, September 2, 2018 4:39 PM
    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 4:36 PM
  • Thank for reply  :-)

    I Follow the same setup which mention in both url  for Oracle OLEDB Provider but hard luck i can't see these driver into Link Server Tab in SQL server Management

    studio Please guide me?

    I reboot my machine still unable to see that OLEDB provider?

    Thank in Advance   

    • Edited by Vikas Salve Sunday, September 2, 2018 5:58 PM ad new line
    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 5:57 PM
  • Thank for reply  :-)

    I Follow the same setup which mention in both url  for Oracle OLEDB Provider but hard luck i can't see these driver into Link Server Tab in SQL server Management

    studio Please guide me?

    I reboot my machine still unable to see that OLEDB provider?

    Thank in Advance   

    Did you install 32 bit or 64 bit version?



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 6:36 PM
  • You need to install both 64-bit AND 32-bit ODAC components in order to be able to see OraOLEDB.Oracle provider and create a Linked Server to Oracle store.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Monday, September 3, 2018 11:38 AM
  • Hi Vikas, 

    Did you install the 64-bit Oracle Client as the tutorial mentioned? 

    http://www.recurringtheme.com/?p=3617



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Tuesday, September 4, 2018 7:11 AM

All replies

  • See

    https://logicalread.com/sql-server-2014-and-oracle-12c-linked-servers-mo01/#.W4wRGM4zapo

    http://www.recurringtheme.com/?p=3617


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Sunday, September 2, 2018 4:39 PM
    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 4:36 PM
  • Thank for reply  :-)

    I Follow the same setup which mention in both url  for Oracle OLEDB Provider but hard luck i can't see these driver into Link Server Tab in SQL server Management

    studio Please guide me?

    I reboot my machine still unable to see that OLEDB provider?

    Thank in Advance   

    • Edited by Vikas Salve Sunday, September 2, 2018 5:58 PM ad new line
    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 5:57 PM
  • Thank for reply  :-)

    I Follow the same setup which mention in both url  for Oracle OLEDB Provider but hard luck i can't see these driver into Link Server Tab in SQL server Management

    studio Please guide me?

    I reboot my machine still unable to see that OLEDB provider?

    Thank in Advance   

    Did you install 32 bit or 64 bit version?



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Sunday, September 2, 2018 6:36 PM
  • Here's a step-by-step walkthrough: Creating a Linked Server for Oracle in 64bit SQL Server

    David



    Microsoft Technology Center - Dallas
    My blog

    Sunday, September 2, 2018 7:57 PM
  • 64 Bit
    Monday, September 3, 2018 4:13 AM
  • I try but hard luck

    After Reboot machine OLEDB is not showing in SQL server

    • Edited by Vikas Salve Monday, September 3, 2018 4:19 AM
    Monday, September 3, 2018 4:13 AM
  • SQL Server:

    Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86) 
    Feb 20 2014 19:20:46 

    Hi Vikas, 

    Your SQL Server is 32-bit. 

    You might need to install the 32-bit ODAC and try again to see what will happen. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 3, 2018 10:27 AM
  • You need to install both 64-bit AND 32-bit ODAC components in order to be able to see OraOLEDB.Oracle provider and create a Linked Server to Oracle store.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Monday, September 3, 2018 11:38 AM
  • I install both 32/64 Bit ODAC now still not showing OraOLEDB.Oracle  in SQL Server
    Tuesday, September 4, 2018 5:15 AM
  • Hi Vikas, 

    Did you install the 64-bit Oracle Client as the tutorial mentioned? 

    http://www.recurringtheme.com/?p=3617



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Vikas Salve Tuesday, September 4, 2018 9:33 AM
    Tuesday, September 4, 2018 7:11 AM
  • Thank You every one. Visakh ,David,Yang.z,philfactor

    i just use this command to install OraOLEDB.Oracle

    install.bat all c:\oracle odac

    Now i'm able to Create link server for Oracle but while i execute query 

    while i select oracle ole db provider sql server crash

    Sample code 

    sp_addlinkedserver  @server = 'OrclDB',@srvproduct = 'Oracle',@provider='OraOLEDB.Oracle',@datasrc='pdborcl'

    exec master.dbo.sp_serveroption  @server=N'OrclDB',@optname=N'rpc out',@optvalue=N'true';

    execute sp_addlinkedsrvlogin  @rmtsrvname='OrclDB',@useself='false',@locallogin=NULL,@rmtuser='hr',@rmtpassword='hr' SELECT * FROM OrclDB..HR.EMPLOYEES


    Tuesday, September 4, 2018 9:45 AM
  • Thank You every one. Visakh ,David,Yang.z,philfactor

    i just use this command to install OraOLEDB.Oracle

    install.bat all c:\oracle odac

    Now i'm able to Create link server for Oracle but while i execute query 

    while i select oracle ole db provider sql server crash

    Sample code 

    sp_addlinkedserver  @server = 'OrclDB',@srvproduct = 'Oracle',@provider='OraOLEDB.Oracle',@datasrc='pdborcl'

    exec master.dbo.sp_serveroption  @server=N'OrclDB',@optname=N'rpc out',@optvalue=N'true';

    execute sp_addlinkedsrvlogin  @rmtsrvname='OrclDB',@useself='false',@locallogin=NULL,@rmtuser='hr',@rmtpassword='hr' SELECT * FROM OrclDB..HR.EMPLOYEES


    How did you do login mapping for your linked server?

    Did you use logins current security context or did you use a sql login and password to which the local logins were mapped?

    The error given above is a very generic error so that doesnt help much in understanding the issue

    This usually occurs when connection is terminated intermittendly


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 4, 2018 10:45 AM
  • I'm able to Create DB to connect Oracle database code as below

       

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 ;
    GO

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 ;
    GO

    sp_addlinkedserver @server = 'OrclDB', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'PDBORCL';
    GO

    exec master.dbo.sp_serveroption  @server=N'OrclDB',@optname=N'rpc out',@optvalue=N'true';
    GO

    sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB', @useself = 'False', @locallogin = NULL, @rmtuser = 'hr', @rmtpassword ='hr';
    GO


    SELECT * FROM OrclDB..HR.EMPLOYEES;

    Error screenshotas below:-

     

    Every Time i execute above query i'm getting above error.


    Wednesday, September 5, 2018 4:35 AM
  • I'm able to Create DB to connect Oracle database code as below

       

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1 ;
    GO

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1 ;
    GO

    sp_addlinkedserver @server = 'OrclDB', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'PDBORCL';
    GO

    exec master.dbo.sp_serveroption  @server=N'OrclDB',@optname=N'rpc out',@optvalue=N'true';
    GO

    sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB', @useself = 'False', @locallogin = NULL, @rmtuser = 'hr', @rmtpassword ='hr';
    GO


    SELECT * FROM OrclDB..HR.EMPLOYEES;

    Error screenshotas below:-

     

    Every Time i execute above query i'm getting above error.


    The error above looks more like connection timeout or network issue to me

    This doesnt directly relate to any login issue


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, September 5, 2018 5:35 AM
  • It's not login issue 

    As you know I fetching Oracle table data using link server.

    Which I was created perviously.

    Every time I execute query.

    SQL server crash 

    I need to restart SQL service again from

    Services.msc


    • Edited by Vikas Salve Wednesday, September 5, 2018 11:09 AM
    Wednesday, September 5, 2018 11:03 AM
  • It's not login issue 

    As you know I fetching Oracle table data using link server.

    Which I was created perviously.

    Every time I execute query.

    SQL server crash 

    I need to restart SQL service again from

    Services.msc


    It can even be this

    https://support.microsoft.com/en-us/help/2295405/sql-server-service-crashes-when-you-run-an-oracle-linked-server-query


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, September 5, 2018 11:23 AM