June 7, 2006 at 8:14 am
I am able to set up a linked server from a sql7db to an oracle10 server and perform some simple select statements. I am having an issue trying to query certain table/views from the oracle db.
example:
SELECT COUNT(*) from ORADB..ORASCHEMA.V_ASSORTMENT
error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset: [Shocked] penRowset returned 0x80004005 - The provider did not give any information about the error.].
Any reason why I can query some tables and not others?
thanks
Todd
June 8, 2006 at 11:25 am
Are you using the Oracle 10 ODBC driver? Can you try using an Oracle 8 driver instead? Since you are using the Microsoft SQL 7 provider for ODBC, a wild guess would be that MSDASQL cannot return a valid error message from Oracle. If you use an older Oracle ODBC driver you might at least get an error that will give you a clue to the problem.
June 9, 2006 at 10:53 am
Actually I was able to get the 'MSDAORA' to work instead of using the 'MSDADQL' by setting up the tnsnames.ora file correctly in the Oracle client tools. After I had that set up I changed the parameters of the linked server to use the 'MSDAORA' provider. Still have problems when trying to select columns that have datatypes sql can not seem to handle. So, using views to exclude these.
June 9, 2006 at 11:06 am
i believe the issue is related to the way oracle 10 handles BLOB and CLOB columns;
if we don't use the updated drivers that are part of the Ora10 client install, we have the same issue in our shop;
if we try and connect to an oracle 10, using drivers that worked fine in oracle 8/9, any table with a BLOB/CLOB will fail with a select *; we have to select specific column names and skipp the BLOB/CLOB fields for developer adhoc queries;
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply