locked
How to connect/retrieve data from Oracle DB in SQL DB RRS feed

  • Question

  • Hi all,

    Is there a way/various methods to connect and retrieve data from Oracle to SQL Server?

    If so, can you suggest me if various possible solution along side with sufficient steps to acheive it. A sample application will suffice.

    Thanks in advance.
    Monday, July 9, 2012 9:34 AM

Answers

All replies

  • Hello,

    You can add a "linked server" pointing to Oracle database, see: How to set up and troubleshoot a linked server to an Oracle database.

    See also: Linking Servers


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Satheesh Variath Monday, July 9, 2012 10:17 AM
    • Marked as answer by Dhanavel Tuesday, July 17, 2012 7:22 AM
    Monday, July 9, 2012 9:38 AM
  • 1) SSIS

    2) Linked Server

    USE MASTER
    GO
    EXEC sp_addlinkedserver
    @server = 'NameForLinkedServer',
    @srvproduct = 'oracle',
    @provider = 'OraOLEDB.Oracle',
    @datasrc = '//full.pc.DomainName/OracleServiceName-or-SID'
    GO
    Exec sp_serveroption  'NameForLinkedServer' , 'rpc' , TRUE
    GO
    Exec sp_serveroption 'NameForLinkedServer' , 'data access' , TRUE
    GO
    Exec sp_serveroption 'NameForLinkedServer' , 'rpc out' , TRUE
    GO
    Exec sp_addlinkedsrvlogin
    @rmtsrvname='NameForLinkedServer',
    @useself= FALSE,
    @rmtuser='remoteusername',
    @rmtpassword='remoteuserpassword'


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, July 9, 2012 9:38 AM
    Answerer
  • Set up Link server to point to Oracle from Sql server. Read this

    http://msdn.microsoft.com/en-us/library/ff772782.aspx


    Many Thanks & Best Regards, Hua Min


    Monday, July 9, 2012 9:44 AM
  • And you can also use replication

    Microsoft Certified Trainer & MVP on SQL Server
    Please "Propose as Answer" if you got an answer on your question, and vote for it as helpful to help other user's find a solution on a similar question quicker.

    Monday, July 9, 2012 1:27 PM
  • Hi Uri Dimant

    Thanks for u r reply 

    One more thing :

    I am using Oracle 11g Express edition 

    I follow this steps 

     USE MASTER
    GO
    EXEC sp_addlinkedserver
    @server = 'TX01VM02-PC',
    @srvproduct = 'oracle',
    @provider = 'OraOLEDB.Oracle',
    @datasrc = '//TX01VM02-PC/OracleServiceXE'
    GO
    Exec sp_serveroption 'TX01VM02-PC' , 'rpc' , TRUE
    GO
    Exec sp_serveroption 'TX01VM02-PC' , 'data access' , TRUE
    GO
    Exec sp_serveroption 'TX01VM02-PC' , 'rpc out' , TRUE
    GO
    Exec sp_addlinkedsrvlogin
    @rmtsrvname='TX01VM02-PC',
    @useself= FALSE,
    @rmtuser='system',
    @rmtpassword='dvssa'

    i have created linked server successfully and  

    When i execute this query ,

    "select * from [TX01VM02-PC].system..sample "

    getting this below error 

    "Msg 7403, Level 16, State 1, Line 1

    The OLE DB provider "OraOLEDB.Oracle" has not been registered."

    Kindly provide your valuable suggestion 

    Thanks in advance 

    Monday, July 9, 2012 3:15 PM
  • Hi Dhanavel,

    Could you please try to reinstall the Oracle Data Access Components? And also modify the system registry settings. For more details, please refer to this thread.


    Best Regards, Ray Chen

    Tuesday, July 10, 2012 5:22 AM
  • Hi all

     I am trying to connect and retrieve data from oracle DB in SQL server using Linked Server.

    I am using the following system configuration

    1.        Windows7 OS
    2.        SOL Server 2008 Enterprise Edition
    3.        Oracle 11g standard edition with 32 bit

    I used the below script to create the linked server in SQL server

    USE MASTER

    GO

    EXEC sp_addlinkedserver

    @server = 'TX01VM02-PC',

    @srvproduct = 'oracle',

    @provider = 'OraOLEDB.Oracle',

    @datasrc = '//TX01VM02-PC/OracleServiceXE'

    GO

    Exec sp_serveroption 'TX01VM02-PC' , 'rpc' , TRUE

    GO

    Exec sp_serveroption 'TX01VM02-PC' , 'data access' , TRUE

    GO

    Exec sp_serveroption 'TX01VM02-PC' , 'rpc out' , TRUE

    GO

    Exec sp_addlinkedsrvlogin

    @rmtsrvname='TX01VM02-PC',

    @useself= FALSE,

    @rmtuser='system',

    @rmtpassword='dvssa'

    I got success message

    But when I tried to execute a query with the created linked server I am getting the following error

    "select * from [TX01VM02-PC].system..sample "

    "Msg 7403, Level 16, State 1, Line 1

    The OLE DB provider "OraOLEDB.Oracle" has not been registered."

    Kindly help me on this.

    Thanks in advance 

    Wednesday, July 11, 2012 6:07 AM
  • Hi Dhanavel,

    Have you tried what I suggested above? Please also double check that Data Source (in your scenario, it is Oracle Service Name or SID) is defined in the file “tnsnames.ora”. This file is located at <Oracle installation dir>\network\admin.


    Best Regards, Ray Chen

    Wednesday, July 11, 2012 8:27 AM
  • Hi all,

    Thanks for your valuable feedback.

    Thursday, July 12, 2012 6:24 AM
  • Hi All,

    i successfully connected and retrieved data from Oracle Db in SQL-Server using linked Server .Right now, I have configured oracle Db (10 g) and SQL server 2008 both in the same system, but i want to connect it from a different system/environment. Is that possible, if yes can you tell me how??

    And one more thing. When i actually install oracle in the system which also had SQL server, i obtained the provider 'oraOLEDB.Oracle'. While i see that it was obtained because i installed in the same system, how do i achieve that when i install it in a remote system. Or how do i connect to the remote system having Oracle through some other means?

    Kindly help me on this.

    Thanks in advance 

    Saturday, July 14, 2012 7:19 AM
  • Make sure the Oracle DB machine is with one pingable/reachable IP inside and you use such IP as the host of the Oracel database.

    Many Thanks & Best Regards, Hua Min

    Saturday, July 14, 2012 7:25 AM
  • i successfully connected and retrieved data from Oracle Db in SQL-Server using linked Server .Right now, I have configured oracle Db (10 g) and SQL server 2008 both in the same system, but i want to connect it from a different system/environment. Is that possible, if yes can you tell me how??

    Yes, you can connect to an Oracle instance running on a different server. In fact that would be the normal operation. It strikes me as rare that you have them on the same machine.

    And one more thing. When i actually install oracle in the system which also had SQL server, i obtained the provider 'oraOLEDB.Oracle'. While i see that it was obtained because i installed in the same system, how do i achieve that when i install it in a remote system. Or how do i connect to the remote system having Oracle through some other means?


    To connect to a linked server, you need an OLE DB provider for that data source. Depending on the data source, it may come with windows or with the data source. Windows includes MSDAORA, an OLE DB provider by Microsoft. However, this provider is old and does not work well with Oracle 10g or later, and you should use Oracle's own provider.

    Exactly how you obtain it and install it I don't know. I would assume that it is available as a separate download/install, and in any case you can install it on your server without making a full install of Oracle. But how? This is a forum for SQL Server, and you would need to ask in an Oracle forum for the gory details. However, for your convenience, I made some quick Googling and found
    http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
    However, I have never worked with Oracle, so at this point I will have leave you alone in the wilderness.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Shulei Chen Tuesday, July 17, 2012 1:29 AM
    Saturday, July 14, 2012 8:46 AM