Problems with connecting an Oracle DB

Hi,
I linked a SQL Server 2005 to an Oracle DB 7.3
I can run a query like
select * from SERVERNAME..TABLENAME
and I receive the data
But if I run
select * from SERVERNAME..SCHEMANAME.TABLENAME
I receive the following message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "SERVERNAME" reported an
error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "SERVERNAME".
And I receive the same message after running
select * from SERVERNAME..VIEWNAME
or
sp_tables_ex @.table_server=SERVERNAME
(I don't understand why the first statement works)
I don't have such problems on a local SQL Server 2000
Any idea?
Thanks
Hello, try
exec sp_addlinkedserver 'linkedorcl', 'Oracle', 'MSDAORA', 'linkedorcl'
go
exec sp_addlinkedsrvlogin 'linkedorcl', false, 'sqluser', 'oracleuser',
'oracleuserpassword'
go
dbcc traceon(7399)
go
SELECT * FROM OPENQUERY(linkedorcl, 'SELECT * FROM oracleuser.SALES')
breno tozo
"Michael" wrote:

> Hi,
> I linked a SQL Server 2005 to an Oracle DB 7.3
> I can run a query like
> select * from SERVERNAME..TABLENAME
> and I receive the data
> But if I run
> select * from SERVERNAME..SCHEMANAME.TABLENAME
> I receive the following message:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDAORA" for linked server "SERVERNAME" reported an
> error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "SERVERNAME".
> And I receive the same message after running
> select * from SERVERNAME..VIEWNAME
> or
> sp_tables_ex @.table_server=SERVERNAME
> (I don't understand why the first statement works)
> I don't have such problems on a local SQL Server 2000
> Any idea?
> Thanks
>
>
>
>
|||Unfortunately it doesn't works.
I still receive this error message:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "linkedorcl".
Any new idea?
Michael
"Breno" wrote:
[vbcol=seagreen]
> Hello, try
> exec sp_addlinkedserver 'linkedorcl', 'Oracle', 'MSDAORA', 'linkedorcl'
> go
> exec sp_addlinkedsrvlogin 'linkedorcl', false, 'sqluser', 'oracleuser',
> 'oracleuserpassword'
> go
> dbcc traceon(7399)
> go
> SELECT * FROM OPENQUERY(linkedorcl, 'SELECT * FROM oracleuser.SALES')
> breno tozo
> "Michael" wrote:
|||There is a good chance that communications with an Oracle 7.3 is no longer
supported. Oracle is at relase 10g2. Even Oracle doesn't talk to a release
that old.
You at least have to be very careful with which MDAC you are using.
Joseph R.P. Maloney, CSP,CCP,CDP
"Michael" wrote:
[vbcol=seagreen]
> Unfortunately it doesn't works.
> I still receive this error message:
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "linkedorcl".
> Any new idea?
> Michael
> "Breno" wrote: