I have an Oracle linked server created on my SQL Server 2008 and it works fine for selecting from an existing table on the linked server using openquery. (The four-part naming does not work - I get "the provider does not expose the necessary interfaces to use a catalog or schema")
I can also do openquery statements for inserting into existing tables on the linked server. But now I want to create a new table - a copy of a local table - on the linked server. Can I use openquery syntax for this? Would it work? Something like:
select * into --- openquery syntax (LNKDSRVR, 'new_table') ---
from local_table;
(The reverse works fine).
Answer by Rob Farley ·
I doubt it, because the hook into Oracle won't support pushing the data in like that. As the error says, the necessary interface bits aren't there.
Try from the other side though - see if there's an Oracle method to hook into SQL.