MS SQL Server - Linking to Oracle9

Author

Linking to Oracle9

Simon Shutter

2005-07-13, 3:23 am

First, pls advise if there is a more appropriate newsgroup for this
question.

We are running SQL Server 2000 on Windows 2003 Server and want to create a
linked server for an Oracle9 data source. BOL describes linking to Oracle
versions 8 and earlier; However, article kb244661
(http://support.microsoft.com/kb/244661/) suggests that a .NET Managed
Oracle Provider for the Oracle9i client exists now. So, should we a) use
the older MDAC OLE DB provider and install Oracle8 client tools on our SQL
Server or b) try to get the new provider to work and install the Oracle9
client tools? Alternatively, should we be using Oracle's Instant Client
solution and/or Oracle's OLE DB Provider. Finally, any references to manual
editing of the SQLNET.ORA and TNSNAME.ORA files specifically for use with
SQL Server linked server methods would be appreciated.

Thanks very much

Sophie Guo [MSFT]

2005-07-13, 7:23 am

Hello,

The following information is for your reference:

a) Use the older MDAC OLE DB provider and install Oracle8 client tools on
our SQL Server

I suggest that you use this way and you can follow the steps in the article
280106 to set up a linked server:

280106 How to set up and troubleshoot a linked server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106

b) Try to get the new provider to work and install the Oracle9 client
tools? Alternatively, should we be using Oracle's Instant Client solution
and/or Oracle's OLE DB Provider.

Please read the following articles:

322158 INFO: Features and Limitations of the .NET Managed Provider for
Oracle
http://support.microsoft.com/?id=322158

321718 How to call Oracle stored procedures in Visual Basic .NET with the
http://support.microsoft.com/?id=321718

You may need to use Visual Studio .NET to work with the .NET anaged
Provider for Oracle.

For questions of Oracle related, please contact Oracle support.

For more information of setup linked server, you can also refer to the
sp_addlinkedserver topic in SQL server Books Online(BOL).

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Simon Shutter

2005-07-13, 11:23 am

Thank you, Sophie, for the clarification

"Sophie Guo [MSFT]" <v-sguo@online.microsoft.com> wrote in message
news:Ei80f65hFHA.944@TK2MSFTNGXA01.phx.gbl...
> Hello,
>
> The following information is for your reference:
>
> a) Use the older MDAC OLE DB provider and install Oracle8 client tools on
> our SQL Server
>
> I suggest that you use this way and you can follow the steps in the
article
> 280106 to set up a linked server:
>
> 280106 How to set up and troubleshoot a linked server to Oracle in SQL
> Server
> http://support.microsoft.com/?id=280106
>
> b) Try to get the new provider to work and install the Oracle9 client
> tools? Alternatively, should we be using Oracle's Instant Client solution
> and/or Oracle's OLE DB Provider.
>
> Please read the following articles:
>
> 322158 INFO: Features and Limitations of the .NET Managed Provider for
> Oracle
> http://support.microsoft.com/?id=322158
>
> 321718 How to call Oracle stored procedures in Visual Basic .NET with the
> http://support.microsoft.com/?id=321718
>
> You may need to use Visual Studio .NET to work with the .NET anaged
> Provider for Oracle.
>
> For questions of Oracle related, please contact Oracle support.
>
> For more information of setup linked server, you can also refer to the
> sp_addlinkedserver topic in SQL server Books Online(BOL).
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
>
>
>

jaylou

2005-07-13, 1:23 pm

Hi All,
I have followed all the steps outlined in your reply. I am getting an
"MSDAORA IDBinitialize error.

I installed Oracle 9.2 client on my SQL server box, we also have Oracle.net
client on the box. I was getting this error when I only had the .net client
so I decided to install 9.2 (since that is the version of my DB)
I have creates an Alias to the Oracle DB, and an ODBC to it as well.

I have been reading every article I can find on linking SQL server to Oracle
and they all say the same.

If there is anything (no matter how obvious) that you may think of, please
please let me know...

TIA,
Joe

Sophie Guo [MSFT]

2005-07-14, 3:23 am

Hello,

Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle
are not supported with any Oracle versions later than 8i. For more
information, read the following article:

244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661

You have to use the older MDAC OLE DB provider and install Oracle8 client
tools on SQL Server.

If the issue still exists, please submit a new post to open a new thread.
Thanks.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

jaylou

2005-07-16, 9:23 am

Thank you I have solved the issue. It was the ODBC connection I wasnt using
the Oracle 9 dll.

"Sophie Guo [MSFT]" wrote:

> Hello,
>
> Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle
> are not supported with any Oracle versions later than 8i. For more
> information, read the following article:
>
> 244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
> http://support.microsoft.com/?id=244661
>
> You have to use the older MDAC OLE DB provider and install Oracle8 client
> tools on SQL Server.
>
> If the issue still exists, please submit a new post to open a new thread.
> Thanks.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Sponsored Links