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

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...

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...

Problem is forming Query

Hello,

I have a table
say SalesLeads. This table is accessed through various front -ends and
it stores data for Sales leads of various products.

This table has a reference to another table(SalesLeadRefTable)

Sample Data in SalesLeads

SalesLeadID Comments RefTableID
-----------------
1 Sample 1

Sample Data in SalesLeadRefTable

ReferenceID TableName TableFieldName
-----------------
1 ProductTable ProductID

I need to form a query which will refer to the refdtableID and form a
dynamic query like

Select Comments , TableName.TableFieldName from SalesLeads,TableName

Please let me know if this can be doen in 1 queryYou could implement a JOIN to one of many tables like this:

SELECT A.keycol, COALESCE(B.col1, C.col1, D.col1) AS col1
FROM Something AS A
LEFT JOIN Table1 AS B
ON A.keycol = B.keycol AND A.entity = 1
LEFT JOIN Table2 AS C
ON A.keycol = C.keycol AND A.entity = 2
LEFT JOIN Table3 AS D
ON A.keycol = D.keycol AND A.entity = 3

Otherwise you would have to use Dynamic SQL inside an EXEC statement.

--
David Portas
SQL Server MVP
--

problem installing MSDE

I get the following message and I am not in safe mode

any ideas

The windows installer service could not be accessed.
This can occur if you are running Windows on safe mode or
if the Windows installer is not correctly installed.
Contact your support personnel for assistance.

I'd search Google for ideas on what to do with that errormessage. It sounds like you have a problem with the Windowsinstaller, not with MSDE.