Connect SQL Server Box to Oracle Box

[Mark]

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

[Larry Miller]

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

[Chris Hylton]

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

[mark]

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
>

[mark]

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
>>
>

[Chris Hylton]

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
>>>
>>
>