T-sql to Oracle database query problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-sql to Oracle database query problems

Author

Topic

thesir
Starting Member

2 Posts

Posted - 2007-11-07 : 19:22:18

I keep recieving a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema."

I'm attempting to set up a linked server from SQL 2005 to Oracle 10.2. I run the following sp_addlinkedserver and sp_addlinkedsrvlogin procedures:

sp_addlinkedserver

@server = 'CUSTOMERLINK',

@provider = 'MSDAORA',

@srvproduct = 'ORACLE',

@datasrc = 'CUSTOMER'

GO

sp_addlinkedsrvlogin

@rmtsrvname = 'CUSTOMERLINK',

@useself = 'False',

@rmtuser = 'CUSTOMER1',

@rmtpassword = 'PASSWORD!'

These procedures complete successfully. I then run a sp_tables_ex procedure:

sp_tables_ex @table_server=CUSTOMERLINK, @table_schema='CUSTOMER1'

This procedure completes successfully and gives me all of the table names in my oracle database that relate to the Customer1 schema.

Then when I go to run a query

select id from [CUSTOMERLINK].[CUSTOMERDB].[CUSTOMER1].[CLIENT] Where name = 'codm'

I recieve a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema." error.

Any suggestions?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-07 : 23:01:32

Tried with openquery?