I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:

EXEC

sp_addlinkedserver

@server

= 'test1',

@srvproduct

= 'Oracle',

@provider

= 'MSDAORA',

@datasrc

= 'testsource'

exec

sp_addlinkedsrvlogin

@rmtsrvname

= 'test1',

@useself

= 'false',

@rmtuser

='sp',

@rmtpassword

='sp'

 

When I execute

select * from test1...COUNTRY

I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."

The 'sp' user I am connecting is the owner of the table. What could be the problem ?

Thanks a lot.

I'm not familiar with Oracle. But if the linked server points to a SQL Server, then when performing a query on a table on the linked server without secifying the owner, by default the owner is dbo. You can try to implicitly specify the owner in your query.

Thanks for your message. I found the problem. Schema name should be in uppercase, not lowercase. I couldn't believe this was the reason :-(

This helped. My owner was not in the right case, found out that the owner in the Linked Server query has to be in uppercase.

 Thanks this helped me.