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?
ThanksHello, 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:
> 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
> >
> >
> >
> >
> >
> >
> >
> >|||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:
> 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:
> > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >