Linking Oracle DB to SQL DB

Hello
Where can I find information on how to link an Oracle
database to a SQL Server. Recently I wrote a small
database in Access linking the tables I need from the an
Oracle database using ODBC drivers but had alot of
connectivity and locking problems. I was advised to link
the Oracle server to SQL Server using Access as frontend.
I am familiar with SQL Server, .net and Access but not
familiar with Oracle. I played with the Linked Server in
SQL an was able to link other SQL servers but not Oracle
giving an MSDAORA error.
I have read articles that you can't just link the Oracle
server using OLEDB or other connectivity sources you must
install a client from Oracle. I downloaded an OUIRelease
after reading the release notes I don't really know what
to do...
Any information is greatly appreciated
Frustrated
Susan
SusanHi Susan,
Try the following article for steps on how to set up a
linked server to Oracle:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Tue, 24 Feb 2004 11:29:34 -0800, "Susan"
<anonymous@.discussions.microsoft.com> wrote:

>Hello
>Where can I find information on how to link an Oracle
>database to a SQL Server. Recently I wrote a small
>database in Access linking the tables I need from the an
>Oracle database using ODBC drivers but had alot of
>connectivity and locking problems. I was advised to link
>the Oracle server to SQL Server using Access as frontend.
>I am familiar with SQL Server, .net and Access but not
>familiar with Oracle. I played with the Linked Server in
>SQL an was able to link other SQL servers but not Oracle
>giving an MSDAORA error.
>I have read articles that you can't just link the Oracle
>server using OLEDB or other connectivity sources you must
>install a client from Oracle. I downloaded an OUIRelease
>after reading the release notes I don't really know what
>to do...
>Any information is greatly appreciated
>Frustrated
>Susan
>Susan|||Thanks for all the info
I was able to install the client with connection success..
but now I still get the error 7399 ole db
provider 'MSDAORA' reported an error. I will go through
the support links you provided. If I can't connect by
tomorrow I'll post again..
Thanks again
Susan
>--Original Message--
>Hi Susan,
>Try the following article for steps on how to set up a
>linked server to Oracle:
>HOW TO: Set Up and Troubleshoot a Linked Server to Oracle
in
>SQL Server
>http://support.microsoft.com/?id=280106
>-Sue
>On Tue, 24 Feb 2004 11:29:34 -0800, "Susan"
><anonymous@.discussions.microsoft.com> wrote:
>
link
frontend.
in
must
OUIRelease
>.
>

server Trouble to Oracle

I'm currently trying to establish a linked server to an Oracle
database.

Setup:
Connecting to 8x version of Oracle
Using 9i client tools (Net Manager)
SQL-Server 2000
Windows 2000

I installed the Oracle 9i client tools, and set up a Service for the
Oracle connection. Testing from the 9i client produces a successfull
connection.

I then opened SQL-Server and created a new Linked Server with the
following setup:

Name - PYR_Link
Provider - Microsoft OLE DB Provider for Oracle
Product Name - PYR
Data Source - PYR (9i Service Name)
Provider string - MSDAORA

I get the unspecific 7399 error that OLE DB provider MSDAORA reported
an error, trace 'Initialize returned 0x80004005'

I've searched through the archives, checked all of the relevant
Microsoft articles I could find, and still have no idea what is wrong.
SQL-Server reports that the provider is registered in the system, but I
can't figure out what I'm missing.

Help or a guide for troubleshooting would be much appreciated.

TimAn update - When I run sp_catalog in Query Analyzer, the error message
is more detailed. It says the provider could nor resolve the service
name. Still strange, considering the Net Manager client connects fine.|||Hi Tim, this error could occur when the oracle domain is not recognised.

Try to find out the Oracle domain (e.g. 'world') and change your linked
server to connect to that domain, like product name PYR.world and data
source PYR.world.

Good luck!
Maurits

<tim.pascoe@.cciw.ca> schreef in bericht
news:1123770210.158827.38320@.g47g2000cwa.googlegro ups.com...
> I'm currently trying to establish a linked server to an Oracle
> database.
> Setup:
> Connecting to 8x version of Oracle
> Using 9i client tools (Net Manager)
> SQL-Server 2000
> Windows 2000
> I installed the Oracle 9i client tools, and set up a Service for the
> Oracle connection. Testing from the 9i client produces a successfull
> connection.
> I then opened SQL-Server and created a new Linked Server with the
> following setup:
> Name - PYR_Link
> Provider - Microsoft OLE DB Provider for Oracle
> Product Name - PYR
> Data Source - PYR (9i Service Name)
> Provider string - MSDAORA
> I get the unspecific 7399 error that OLE DB provider MSDAORA reported
> an error, trace 'Initialize returned 0x80004005'
> I've searched through the archives, checked all of the relevant
> Microsoft articles I could find, and still have no idea what is wrong.
> SQL-Server reports that the provider is registered in the system, but I
> can't figure out what I'm missing.
> Help or a guide for troubleshooting would be much appreciated.
> Tim

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*
.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll tha
t
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
& #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> & #123;HKEY_LOCAL_MACHINE\SOFTWARE\Microso
ft\MSDTC\MTxOCI}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David

server to Oracle 9.2 database

I've read KB Article 280106 about creating a Linked Server to an Oracle
database. This article does reference any Oracle database higher than 8.1.*.
I want to link to a 9.2.0.1 database.
The article references loading the Oracle client software for 8.1 on the
Sqlserver machine.
Is this possible to create the link server if I load the client software for
9.2 on the Sqlserver machine? If it is, is it as easy as changing to
registry settings referenced in the Article for the 8.1 database from using
the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll that
come with the 9.2 client software?
The other option is if I load the 8.1 client software on the Sqlserver
machine to connect to the 9.2 database, would this work?
"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> I've read KB Article 280106 about creating a Linked Server to an Oracle
> database. This article does reference any Oracle database higher than
> 8.1.*.
> I want to link to a 9.2.0.1 database.
> The article references loading the Oracle client software for 8.1 on the
> Sqlserver machine.
> Is this possible to create the link server if I load the client software
> for
> 9.2 on the Sqlserver machine?
Yes.

>If it is, is it as easy as changing to
> registry settings referenced in the Article for the 8.1 database from
> using
> the oraclient8.dll and orasql8.dll to the oraclient9.dll and orasql9.dll
> that
> come with the 9.2 client software?
Not necessary to change the registry.. The 9i client, or the 10g client
will work for linked server.

> The other option is if I load the 8.1 client software on the Sqlserver
> machine to connect to the 9.2 database, would this work?
>
It should work, yes. But you should probably use the 9i client.
David
|||Thanks for your response
You are telling me that with the 9.2 client software that there is no
registry changes needed.
When I look at the registry setting for
{HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
OracleXaLib value that is set by default is a file that don't exist in the
ORACLE_HOME/bin directory. Is this file not needed?
The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
database, should it be oraclient9.dll for the 9.2 database?
"David Browne" wrote:

> "Paul R" <Paul R@.discussions.microsoft.com> wrote in message
> news:47FF64BC-973E-43A3-B25B-7D2A32AFCE38@.microsoft.com...
> Yes.
>
> Not necessary to change the registry.. The 9i client, or the 10g client
> will work for linked server.
>
> It should work, yes. But you should probably use the 9i client.
> David
>
>
|||"Paul R" <Paul R@.discussions.microsoft.com> wrote in message
news:9BE76BF3-045E-4913-AFDD-6357E74A12B8@.microsoft.com...
> Thanks for your response
> You are telling me that with the 9.2 client software that there is no
> registry changes needed.
> When I look at the registry setting for
> {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I}, the file for
> OracleXaLib value that is set by default is a file that don't exist in the
> ORACLE_HOME/bin directory. Is this file not needed?
> The OracleXaLib by default is xa73.dll. This looks like a DLL for the 7.X
> database, should it be oraclient9.dll for the 9.2 database?
>
Those settings should probably match your Oracle Client install version (not
the database version). Note that for the 8i client and 9 client, the OCI
library has the same name (oci.dll). This should be enough for basic
connectivity. For distributed transactions (XA and DTC) you may need to
configure the other registry keys. For basic linked server functionality
you do not need to change the registry.
David