Using 4 part names against Oracle Linked Server
-
I am trying to use 4 part naming against an Oracle database using the Oracle Instant Client. Although I can access using openquery this is not a good solution for me. Does anyone have experience of this or can tell me if there is any way to tell if this would be supported or not.
I have used this method for Oracle before but not using this client software. The syntax I use is
select * from LINKED_SERVER..SCHEMA.TABLE
The syntax usually works and returns the most sensible error which is :
Server: Msg 7318, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an invalid column definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid column definition.].
Any advice would be welcome.
-
Hi,
you could be facing this problem due to the datatype support.
please check the documentation of the driver to see what data types are supported.
also ensure that you are using the latest provider for Oracle Client.
-- Parag
-
Unfortunately the documentation doesn't make enough sense to me and we are using the free Oracle instant client. We have made progress by changing to OLE DB for Oracle but now we have another error against some files. Does anyone know if there is a cure for this?
-
Humm ...
Is this in .NET ? If so why don't you use the Oracle Provider by Oracle ... I believe you can find it's download somewhere in their web site!
I haven't tested it yet but, by the general talking from some of my «work partners», it's the only provider that really works in setting a usable layer between .NET and the Oracle client! Also your problems can relate to the version of your Oracle client! The latest is the 10g one!! At least the one I have here ... Haven't tested it yet ...
Hope it helps ...
-
I'm not a .net programmer. We are connecting from SQL 2000 using a linked server, this in turn is used by DTS to read into a SQL database. What we've downloaded is the latest I believe (just got it last week).
-
the four part name should be:
linked server name..schema name.table name
ex. linked server name = ORACLEPROD
schema name = DBOWNER
table name = TBLA
four part name should be ORACLEPROD..DBOWNER.TBLA
NOTE: the four part name MUST be in CAPS for it to work (bug in sql2000) , also note the two .. between linked server name and schema name
-
Thanks,
The problem is not related to these points, which I know well and are good points to make, but something to do with the Oracle data types I believe (which I have no control over). Some tables read fine, others don't. I'm looking for some sort of setting that will force the method to work in all cases.
-
Search the BOL for "lazy schema validation" and read the "Configuring OLE DB Providers for Distributed Queries" topic. This may apply here, but you are correct that there are certain data types that are not supported by the MS provider.
If found using the MS OLE DB Provider for Oracle (as opposed to Oracle Provider for OLE DB) to be most stable and the one MS recommends. It has the added benefit of being able to easily perform distrubuted transactions w/o installing anything other than MSDTC.
This is also a good article on the various options on a linked server. When using 4 part names you should take special consideration of the "Use Remote Collation" and "Collation Name" settings.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply