November 7, 2007 at 4:57 pm
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?
November 8, 2007 at 10:47 am
Is the table name and Column uppercase on the Oracle server? These should match the results of your sp_tables_ex @table_server=CUSTOMERLINK, @table_schema='CUSTOMER1' command.
Have a look at these two articles:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
November 12, 2007 at 7:55 am
From my experience it may help by using skipping the database name in the four part name. For example:
[LinkedServer]..[Schema].[Table]
November 12, 2007 at 8:34 am
Yeah I took a look at the microsoft support site. I have everything set up properly. I did get it working using the OpenQuery(linkedServer, SQL). I thought you could use either syntax. I guess I'll just go with the openquery().
Thank you
November 12, 2007 at 2:56 pm
If you look under your linked servers and the database name is default you should query the linked server as such. [linked server]..[schema].
, as SQL Server will know to use the default database name.
Note: the two periods after [linked server]
I had this vary issue and this resolved my problem.
Adam
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply