Connect SQL Server Box to Oracle Box


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Connect SQL Server Box to Oracle Box

Hybrid View

  1. #1
    Mark Guest

    Connect SQL Server Box to Oracle Box


    Hello, I'm maintaining an application that is VB6 over SQL Server. I need
    to connect to an Oracle box (817) to get some data. What do I need to do?
    I'm sure there is some kind of driver and I'll need to create a DSN once
    I have that driver on the SQL Server box correct?

    THanks

    Mark

  2. #2
    Larry Miller Guest

    Re: Connect SQL Server Box to Oracle Box

    Mark,
    What do you need to do?

    Data Transformation Services (DTS) may work for you. You can connect your
    SQL Server box to the Oracle box and it can upload/download data to/from
    either server. You can use the SQL Agent to schedule the DTS job if you
    need to do it on a regular basis.

    See Books-On-Line (BOL) for more information.

    HTH.

    --
    Larry Miller
    MCSD, Microsoft MVP Visual FoxPro
    Bifrost Solutions

    "Mark" <marktaylor@globalsolar.com> wrote in message
    news:3d2dc503$1@10.1.10.29...
    >
    > Hello, I'm maintaining an application that is VB6 over SQL Server. I need
    > to connect to an Oracle box (817) to get some data. What do I need to do?
    > I'm sure there is some kind of driver and I'll need to create a DSN once
    > I have that driver on the SQL Server box correct?
    >
    > THanks
    >
    > Mark




  3. #3
    Chris Hylton Guest

    Re: Connect SQL Server Box to Oracle Box


    Check out Linked Servers in SQL Server BOL. If you need live data from Oracle
    queried directly from VB through the SQL Server box for some reason, the
    linked server will be easier to deal with than DTS.

    Just remember to use OPENQUERY to pull the data so that it uses 'passthru',
    the indexes in Oracle are not always used by a Linked Server and as a result,
    you can really take a performance hit (potentially huge) if you attempt to
    allow SQL Server to process the Oracle data in a query on the SQL Server
    box.

    Chris



    "Mark" <marktaylor@globalsolar.com> wrote:
    >
    >Hello, I'm maintaining an application that is VB6 over SQL Server. I need
    >to connect to an Oracle box (817) to get some data. What do I need to do?
    > I'm sure there is some kind of driver and I'll need to create a DSN once
    >I have that driver on the SQL Server box correct?
    >
    >THanks
    >
    >Mark



  4. #4
    mark Guest

    Re: Connect SQL Server Box to Oracle Box


    Chris and Larry, thanks for the suggestions. I do need live data and I have
    been able to test the idea of linked server using an Access database but
    have not been able to duplicate the feat with Oracle. I'm a little confused
    on how to point to the Oracle DB itself. When I fill in the Linked Server
    properties selecting the Microsoft OLE DB for Oracle, the catalog text box
    is disabled. This is my understanding of how to fill in the properties in
    Enterprise Manager:

    The Linked Server property is my own name that I choose to reference the
    server with.

    The Other Datasource should be set to Microsoft OLE DB for Oracle

    The Product Name is optional and unnecessary

    The Datasource is the actual name of the Server that hosts the Oracle DB.

    I have not filled in the Provider String. Perhaps this is what I need to
    do?

    Location and Catalog are currently disabled.

    That's how things look right now but I get Error 7399 when I try to use the
    linked server.

    Your kind assistance is greatly appreciated.

    Best Regards

    Mark


    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Check out Linked Servers in SQL Server BOL. If you need live data from

    Oracle
    >queried directly from VB through the SQL Server box for some reason, the
    >linked server will be easier to deal with than DTS.
    >
    >Just remember to use OPENQUERY to pull the data so that it uses 'passthru',
    >the indexes in Oracle are not always used by a Linked Server and as a result,
    >you can really take a performance hit (potentially huge) if you attempt

    to
    >allow SQL Server to process the Oracle data in a query on the SQL Server
    >box.
    >
    >Chris
    >
    >
    >
    >"Mark" <marktaylor@globalsolar.com> wrote:
    >>
    >>Hello, I'm maintaining an application that is VB6 over SQL Server. I need
    >>to connect to an Oracle box (817) to get some data. What do I need to

    do?
    >> I'm sure there is some kind of driver and I'll need to create a DSN once
    >>I have that driver on the SQL Server box correct?
    >>
    >>THanks
    >>
    >>Mark

    >



  5. #5
    mark Guest

    Re: Connect SQL Server Box to Oracle Box


    I forgot to add that we are using SQL Server 7. The data is not needed for
    update, it's a read only thing I need to do.

    THanks

    Mark



    "mark" <marktaylor@globalsolar.com> wrote:
    >
    >Chris and Larry, thanks for the suggestions. I do need live data and I

    have
    >been able to test the idea of linked server using an Access database but
    >have not been able to duplicate the feat with Oracle. I'm a little confused
    >on how to point to the Oracle DB itself. When I fill in the Linked Server
    >properties selecting the Microsoft OLE DB for Oracle, the catalog text box
    >is disabled. This is my understanding of how to fill in the properties in
    >Enterprise Manager:
    >
    >The Linked Server property is my own name that I choose to reference the
    >server with.
    >
    >The Other Datasource should be set to Microsoft OLE DB for Oracle
    >
    >The Product Name is optional and unnecessary
    >
    >The Datasource is the actual name of the Server that hosts the Oracle DB.
    >
    >I have not filled in the Provider String. Perhaps this is what I need to
    >do?
    >
    >Location and Catalog are currently disabled.
    >
    >That's how things look right now but I get Error 7399 when I try to use

    the
    >linked server.
    >
    >Your kind assistance is greatly appreciated.
    >
    >Best Regards
    >
    >Mark
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Check out Linked Servers in SQL Server BOL. If you need live data from

    >Oracle
    >>queried directly from VB through the SQL Server box for some reason, the
    >>linked server will be easier to deal with than DTS.
    >>
    >>Just remember to use OPENQUERY to pull the data so that it uses 'passthru',
    >>the indexes in Oracle are not always used by a Linked Server and as a result,
    >>you can really take a performance hit (potentially huge) if you attempt

    >to
    >>allow SQL Server to process the Oracle data in a query on the SQL Server
    >>box.
    >>
    >>Chris
    >>
    >>
    >>
    >>"Mark" <marktaylor@globalsolar.com> wrote:
    >>>
    >>>Hello, I'm maintaining an application that is VB6 over SQL Server. I

    need
    >>>to connect to an Oracle box (817) to get some data. What do I need to

    >do?
    >>> I'm sure there is some kind of driver and I'll need to create a DSN once
    >>>I have that driver on the SQL Server box correct?
    >>>
    >>>THanks
    >>>
    >>>Mark

    >>

    >



  6. #6
    Chris Hylton Guest

    Re: Connect SQL Server Box to Oracle Box


    Mark, check out the following article on the MS knowledgebase and let me know
    if it works. I have made connections to Oracle before from both SQL 7 and
    2000, but I don't have access to both in the same place currently to look
    at the parameters.

    The following article shows a specific example of connecting to Oracle from
    SQL 7 to do a heterogeneous query. Just read through the linked server part.

    http://msdn.microsoft.com/library/en...asp?frame=true

    Once you get it working, I would definitely recommend using OPENQUERY (not
    discussed in this article) vs. a straight select statement against the linked
    server. If the Oracle tables are large, or your query is complex, SQL Server
    doesn't do a very good job of using the Oracle indexes all the time and OPENQUERY
    uses a 'pass-thru' query making Oracle actually run the query...works great
    for hitting large tables or running complex queries.

    Chris

    "mark" <mark@globalsolar.com> wrote:
    >
    >I forgot to add that we are using SQL Server 7. The data is not needed

    for
    >update, it's a read only thing I need to do.
    >
    >THanks
    >
    >Mark
    >
    >
    >
    >"mark" <marktaylor@globalsolar.com> wrote:
    >>
    >>Chris and Larry, thanks for the suggestions. I do need live data and I

    >have
    >>been able to test the idea of linked server using an Access database but
    >>have not been able to duplicate the feat with Oracle. I'm a little confused
    >>on how to point to the Oracle DB itself. When I fill in the Linked Server
    >>properties selecting the Microsoft OLE DB for Oracle, the catalog text

    box
    >>is disabled. This is my understanding of how to fill in the properties

    in
    >>Enterprise Manager:
    >>
    >>The Linked Server property is my own name that I choose to reference the
    >>server with.
    >>
    >>The Other Datasource should be set to Microsoft OLE DB for Oracle
    >>
    >>The Product Name is optional and unnecessary
    >>
    >>The Datasource is the actual name of the Server that hosts the Oracle DB.
    >>
    >>I have not filled in the Provider String. Perhaps this is what I need

    to
    >>do?
    >>
    >>Location and Catalog are currently disabled.
    >>
    >>That's how things look right now but I get Error 7399 when I try to use

    >the
    >>linked server.
    >>
    >>Your kind assistance is greatly appreciated.
    >>
    >>Best Regards
    >>
    >>Mark
    >>
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>Check out Linked Servers in SQL Server BOL. If you need live data from

    >>Oracle
    >>>queried directly from VB through the SQL Server box for some reason, the
    >>>linked server will be easier to deal with than DTS.
    >>>
    >>>Just remember to use OPENQUERY to pull the data so that it uses 'passthru',
    >>>the indexes in Oracle are not always used by a Linked Server and as a

    result,
    >>>you can really take a performance hit (potentially huge) if you attempt

    >>to
    >>>allow SQL Server to process the Oracle data in a query on the SQL Server
    >>>box.
    >>>
    >>>Chris
    >>>
    >>>
    >>>
    >>>"Mark" <marktaylor@globalsolar.com> wrote:
    >>>>
    >>>>Hello, I'm maintaining an application that is VB6 over SQL Server. I

    >need
    >>>>to connect to an Oracle box (817) to get some data. What do I need to

    >>do?
    >>>> I'm sure there is some kind of driver and I'll need to create a DSN

    once
    >>>>I have that driver on the SQL Server box correct?
    >>>>
    >>>>THanks
    >>>>
    >>>>Mark
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center