Linked Server to Oracle

Linked Server to Oracle

Post by Kevi » Sun, 07 Apr 2002 22:52:25



I am attempting to setup a linked server from SQL Server
2000 to Oracle 9i.  Both databases are on the same server,
which is running Windows 2K Server.  I have read BOL and
performed all steps, but when I attempt to open the linked
server in Enterprise manager, I receive an error that
reads "Error 7399: OLE DB Provider 'MSDAORA' Reported an
Error".  I have the correct version of the Oracle client
tools and SQL*Net.  I have created an alias to the Oracle
instance in the TNSNames.ora file and use that as the data
source in my linked server.  When creating a login for the
linked server, I specified 'sa' as the SQL account, and a
user that I had created in Oracle.  I am not really sure
what I have missed.  Does anyone know of any documentation
other than what MS has?  Also, if anyone can explain how
the SQL login that I specify maps to the Oracle login,
this may help me more fully understand the process.  Any
help at all, no matter how small, will be greatly
appreciated.  

Thanks in advance, Kevin  

 
 
 

Linked Server to Oracle

Post by Sue Hoegemeie » Wed, 10 Apr 2002 06:46:18


Have you tried using tnsping while logged into the SQL
Server box to make sure you can connect to the Oracle server
with the configuration? If that is okay then you just need
to configure the linked server.
There is a article for troubleshooting Oracle linked
servers:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server (Q280106)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q280106
The way you have it mapped, the sa login would be able to
connect to Oracle and if that's all you defined then that's
the only login that can use the linked server...if that's
what you want. Remote User and Remote Password is the
login/password that will be used on the Oracle database.
Local login is the SQL Server login that will use the Remote
User/Login that you specifiy.

--Sue

On Sat, 6 Apr 2002 05:52:25 -0800, "Kevin"


>I am attempting to setup a linked server from SQL Server
>2000 to Oracle 9i.  Both databases are on the same server,
>which is running Windows 2K Server.  I have read BOL and
>performed all steps, but when I attempt to open the linked
>server in Enterprise manager, I receive an error that
>reads "Error 7399: OLE DB Provider 'MSDAORA' Reported an
>Error".  I have the correct version of the Oracle client
>tools and SQL*Net.  I have created an alias to the Oracle
>instance in the TNSNames.ora file and use that as the data
>source in my linked server.  When creating a login for the
>linked server, I specified 'sa' as the SQL account, and a
>user that I had created in Oracle.  I am not really sure
>what I have missed.  Does anyone know of any documentation
>other than what MS has?  Also, if anyone can explain how
>the SQL login that I specify maps to the Oracle login,
>this may help me more fully understand the process.  Any
>help at all, no matter how small, will be greatly
>appreciated.  

>Thanks in advance, Kevin  


 
 
 

Linked Server to Oracle

Post by Darren Elve » Fri, 12 Apr 2002 07:24:21


Kevin

I have just come across the same issue and have resolved it with a little
work:

1) You need to do a registry hack, as documented in "HOW TO: Set Up and
Troubleshoot a Linked Server to Oracle in SQL Server (Q280106)"
however where it refers to the registry key being 8.1 on W2K the key values
for 9i should be as follows (I would recommend that MS update the article);
    "OracleXaLib"="oraclient9.dll"
    "OracleSqlLib"="orasql9.dll"
    "OracleOciLib"="oci.dll"

2) Your linked server configuration in SQL should be as follows:
    Under the General Tab:
        Provider Name = Microsoft OLE DB Provider For Oracle
        Product Name = Oracle
        Data Source = YOUR TNS NAME ENTRY (Here's the Key)
        Provider String = MSDAORA

This should get you linked.

Good Luck

Darren


Quote:> I am attempting to setup a linked server from SQL Server
> 2000 to Oracle 9i.  Both databases are on the same server,
> which is running Windows 2K Server.  I have read BOL and
> performed all steps, but when I attempt to open the linked
> server in Enterprise manager, I receive an error that
> reads "Error 7399: OLE DB Provider 'MSDAORA' Reported an
> Error".  I have the correct version of the Oracle client
> tools and SQL*Net.  I have created an alias to the Oracle
> instance in the TNSNames.ora file and use that as the data
> source in my linked server.  When creating a login for the
> linked server, I specified 'sa' as the SQL account, and a
> user that I had created in Oracle.  I am not really sure
> what I have missed.  Does anyone know of any documentation
> other than what MS has?  Also, if anyone can explain how
> the SQL login that I specify maps to the Oracle login,
> this may help me more fully understand the process.  Any
> help at all, no matter how small, will be greatly
> appreciated.

> Thanks in advance, Kevin

 
 
 

1. Reliable link to Oracle server (linked, server, oracle)

Thanks,

I have a SQL Server 7 server from which I can establish a link to an Oracle
(8) server.  That works - sort of.

I get errors when trying certain queries:

Any aggregate query fails with an 'out of memory' error.
I can do a simple:

 SELECT COUNT(*) FROM <server>..<schema>.<table>

...but this fails if I try to include a table field, and GROUP BY that field
on the result.

My question is:

Does anyone know where to go to find what is required (MDAC versions, ODBC
drivers & versions, etc), that will reliably work between these 2 servers?
I can find bits & pieces and MS KB articles on particular issues, but no
definitive information on how to make the connection function?

TIA,
Tim Cornwell

2. Audit of data modification

3. Linked Server from SQL Server to Oracle

4. Stored Procedure in Red Bricks

5. Oracle linked server in SQL Server 7

6. ASP limitations - am I asking the right newsgroup?

7. Sql Server 2000 - trouble linking to Oracle 7.3 Server

8. collation problem when upgrading from 7.0 to 2000

9. URGENT: SQL Server 2000 to Oracle 9i Linked server problem

10. 20350-CO-Denver-ORACLE-ORACLE Case Tools-ORACLE Financials-ORACLE Forms-ORACLE H

11. 29512-TN-Memphis-ORACLE-ORACLE Forms-ORACLE Tools-ORACLE HR-ORACLE Case Tools-CONSULTANTS