Oracle 8i linked server problems 1

[ecobb]

I'm trying to set up a linked server to an Oracle 8i database. It looks like I have everything set up correctly, in Enterprise Manager I can see the tables and views from the Oracle server. But whenever I try to run the following query, I get an error.

Here's the Query:
Select cust_code
From OracleDB..User.TableName

Here's the error:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.

Does anyone have any idea as to what this could be? I've been searching the MS web site, but so far I haven't been able to find anything.

Thanks!!


Hope This Helps!

Ecobb
- I hate computers!

 

[JamesLean]

You need to qualify the table in the form (note you cannot omit any parts):

SELECT * FROM server.database.owner.table

--James

 

[ecobb]

According to Books Online: "Tables in an Oracle linked server must be referenced using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName. For example, this SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:

SELECT *
FROM OrclDB..MARY.SALES

"

But you're saying that I need specify the database name, even thought it's already been specified in the datasource connection for the linked server, right?

Thanks, I'll give it a try and let you know how it goes.

Hope This Helps!

Ecobb
- I hate computers!

 

[ecobb]

I tried adding the database name, and now I get:

Server: Msg 7312, Level 16, State 1, Line 2
Invalid use of schema and/or catalog for OLE DB provider 'OraOLEDB.Oracle'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].


Any ideas? I think it may be a permissions issue on the Oracle end, but I wanted to check here as well and see if anyone had seen this before.



Hope This Helps!

Ecobb
- I hate computers!

 

[JamesLean]

Sorry for the misinformation - I was thinking of normal SQL linked servers with that 4 part qualified name.

As you say, it could be a permissions problem. I would chek that next and post back if still not working

--James

 

[ecobb]

Thanks James. For anyone interested, I finally found it.
To start with, here is an excellent article on how to set up a linked server to an Oracle Database:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=22264

For whatever reason, I couldn't make it work with Oracle's OLE DB provider, so I had to use SQL Server's 'MSDAORA', which opened up a whole new set of errors.

After going through this article:

http://www-tcsn.experts-exchange.com/Databases/Oracle/Product_Info/Q_20740018.html

I finally figured it out.

I found out that it was an entry in the sqlnet.ora (oracle client tools on my local PC) for the "SQLNET.AUTHENTICATION_SERVICES= (NTS)" which needed to be set to "none" instead of "NTS". Once I changed that, I was able to query the linked server using SQL Server's 'MSDAORA' OLE DB provider.

Don't know what that means, don't know why it worked, but I can query the Oracle database from SQL Server/Query Analyzer so I'm happy!

Hope This Helps!

Ecobb
- I hate computers!

 

[chrissie1]

And this was what I was looking for for all these months.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting