Linked Sever with Oracle Oledb provider query failed!

I'v setuped a Oracle8.16 linked server througth oracle oledb provider:
OraOLEDB.Oracle. When I use this linked server to get data, SqlServer
gave the following error message:
"OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error."
But when I change the OraOLEDB.Oracle provider to MSDAORA,
none error occured!
Can somebody pls. guide me to resolve the issue?
many thanks in advance?Here is the code I used to create a linked server and then select data
set nocount on
go
declare @.server sysname
declare @.userid varchar(10)
declare @.pswd varchar(10)

set @.server = 'ATHENA_ORA'
set @.userid = 'system'
set @.pswd= 'manager'

exec sp_dropserver @.server = @.server , @.droplogins ='droplogins'

EXEC sp_addlinkedserver
@.server = @.server,
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'DBS_Athena'

exec sp_addlinkedsrvlogin @.server, 'FALSE', NULL, @.userid, @.pswd

exec sp_linkedservers
GO

SELECT * FROM ATHENA_ORA..SCOTT.DEPT

A couple of points, @.datasrc = 'DBS_Athena', 'DBS_Athena' is defined in my TNSNAMES.ORA, which was done by using Net8 or SQL*Net. Also I had to put the schema.tablename in uppercase. SELECT * FROM ATHENA_ORA..SCOTT.DEPT worked, but SELECT * FROM ATHENA_ORA..SCOTT.dept or SELECT * FROM ATHENA_ORA..scott.DEPT did not work.

servers Issues

I have a NAMES server and APPLICATION sever. I created a linked server
reference on APPPLICATION to point to NAMES. I did map the APPLICATION
user/password to that on NAMES
Issue : when I click on TABLES in the link server tree all I can see is
tables in the master database. The user/password I pass to the linked server
has rights to multiple databases on NAMES. The user in NAMES default databas
e
is master, but I would think I could see all db on NAMES that the user I
passed had access to.
The .NET query designer just tells me that the login failed when I try to
save the SP or on some occasions that it failed to start a distributed
transaction b/c login failed.
Ive tried reading up on linked servers on MS website and there seem to be so
many retractions and configurations that need to be made Im starting to get
. I thought this was going to be easy or am i misunderstanding. Also
the MSDN page for linked servers seems to imply that you can only do select
and updates from a linked server and not inserts. Is this true? Our NAMES
database is used by multiple applications and multiple databases. Because of
performance I realize that NAMES database would work better on its own serve
r.
JP
.NET Software DevelperJP,
Change the default database for the linkedserver login on the linked server
to database on which you'd like to see the tables listed. You can use EM or
sp_defaultdb.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>I have a NAMES server and APPLICATION sever. I created a linked server
> reference on APPPLICATION to point to NAMES. I did map the APPLICATION
> user/password to that on NAMES
> Issue : when I click on TABLES in the link server tree all I can see is
> tables in the master database. The user/password I pass to the linked
> server
> has rights to multiple databases on NAMES. The user in NAMES default
> database
> is master, but I would think I could see all db on NAMES that the user I
> passed had access to.
>
> The .NET query designer just tells me that the login failed when I try to
> save the SP or on some occasions that it failed to start a distributed
> transaction b/c login failed.
> Ive tried reading up on linked servers on MS website and there seem to be
> so
> many retractions and configurations that need to be made Im starting to
> get
> . I thought this was going to be easy or am i misunderstanding.
> Also
> the MSDN page for linked servers seems to imply that you can only do
> select
> and updates from a linked server and not inserts. Is this true? Our NAMES
> database is used by multiple applications and multiple databases. Because
> of
> performance I realize that NAMES database would work better on its own
> server.
>
> --
> JP
> .NET Software Develper|||Is there no way to see multiple databases on the linked server? In addtion
can I also see views and tables and not SPs?
JP
.NET Software Develper
"Jerry Spivey" wrote:

> JP,
> Change the default database for the linkedserver login on the linked serve
r
> to database on which you'd like to see the tables listed. You can use EM
or
> sp_defaultdb.
> HTH
> Jerry
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:22FE02D9-8576-43E5-9EA7-AEB55C5E5704@.microsoft.com...
>
>|||JP,
The treeview in EM shows the tables and views for the default database of
the LS login on LS. EM is not customizable but you could use .NET and
SQL-DMO to create your own SQL admin/treeview application. Optionally, if
you're just trying to view the objects on the LS and the LS is a SQL Server,
why not just register it in EM. Outside of that you'd have to run T-SQL to
get what you're looking for qualifying the database in the OPENQUERY
statement or the 4-part name. i.e., you could use sp_stored_procedures or
some custom script to show the procs.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:3DF957D0-E428-48F2-ADC8-B756B8156E8C@.microsoft.com...
> Is there no way to see multiple databases on the linked server? In addtion
> can I also see views and tables and not SPs?
> --
> JP
> .NET Software Develper
>
> "Jerry Spivey" wrote:
>

servers

I have created a link between server A and server B
(sever A contains the link info)
I set the default database of the user on server B to be the database I want
to link to on server B.
Inside Enterprise Manager I can view the tables on server B via the link
icon on server A.
When I start Visual Studio and go to the server explorer to design a query
using the GUI interface, when I try to code:
SEVER_NAME.DATABASE.DBO.TABLE or
SERVER_NAME.DBO.TABLE
(Thinking that if the user on the linked servers default database is set,
then no need to specified in the query?)
inside the query it aliases the table name but not columns display in the
design view, just the *. Trying to run the query said that the Name can only
be three parts or invalid object name SERVER_NAME.dbo.TABLE
Like I said I can view the tables via the linking server though EM. My
developers need to be able to use the VS gui to design their queries. I ran
into situations in the past were if I typed the query straight (not using
gui), and try to save it, I would get a message stated that the MS DTC could
not be started, when I know for a fact both server A & B have the DTC runnin
g.
Can anyone provide assistance?SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manager
(SQL 2005).
ML
http://milambda.blogspot.com/|||SQL Server
DTC
and the SQL Ageent are all running.
Where else do I need to look or configure
--
JP
.NET Software Develper
"ML" wrote:

> SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manage
r
> (SQL 2005).
>
> ML
> --
> http://milambda.blogspot.com/