Problem retrieving data from a linked server to Oracle

Don't know if this is the correct section.
I set up a linked server to a Oracle 9.2 from SQL 2005.
How can I see the tables that can be accessed from SQL? in SQL 2000 I just clicked on the linked server to see tables and views, but in SQL 2005 Management Studio I cannot see them.

Then to see if the linked server is set up correctly I tryed some scripts:
I do a SELECT * FROM RFVO..POKER.TABLE1 and I get all the data

then I tryed the same on a different table
SELECT * FROM RFVO..POKER.BOLLE and get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "RFVO" supplied inconsistent metadata for a column. The column "TB_CAMBIO" (compile-time ordinal 32) of object ""POKER"."BOLLE"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

What does it mean? How can I solve it?
Do I have to test all the tables in the linked server to be sure I can access data or is there a smarter way?

TIA

The functionality to view remote tables/views for linked servers is not available in SQL2005 Management Studio. You can use the system stored procedure 'sp_tables_ex' with linked server name to retrieve remote tables/views information.

The error message from the select query is a provider error. The provider reports that the column "TB_CAMBIO" has Unicode string datatype (DBTYPE_WSTR or 130) when the query is compiled and float datatype (DBTYPE_R8 or 5) when the query is execute. You can workaround the provider error by executing a pass-through query using OPENQUERY or EXEC AT. The other alternative is to use the Oracle's OLE DB provider instead of MS ORA provider.

|||Thanks for your answer!
I cannot install Oracle OLE DB provider on that machine. by now
I tried linking Oracle server using OLE DB for ODBC driver and creating an ODBC using microsoft driver on the SQL 2005 machine but now I have a different error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

Any suggestion on how to set up a linked server to oracle will be geatly appreciated!
IgorB|||see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Try using Openquery

|||I installed the Oracle OLE DB Provider, and everything seem to work correctly!
I think that the linked server will work also with OLE DB for ODBC and I am tryuing to do a test for performance reasones.

The error Error converting data type DBTYPE_DBTIMESTAMP to datetime is a data error: in a datetime field there is a strange value. Corrected the wrong value the select I posted in the first message now works.|||

FHANLON wrote:

see

www.sqlservercentral.com/columnists/hji/oracleandsqlserverdatainteroperabilityissuespart1.asp

Not this issue: there are no TIMESTAMP field (the error message is misleading), only DATE fields in Oracle.

FHANLON wrote:

Try using Openquery

I don't want to use Openquery, because I should rewrite a lot of stored procedures...sql