In SQL server, how can I query an Oracle Timestamp column over a Linked server connection?

Question

The query I have made in oracle does not work with linked server with sql server 2008.

The OLE DB provider "MSDAORA" for linked server "ORACLE" supplied invalid metadata for column "DATETIME_INS". The data type is not supported.

The query:

select * from ORACLE..U_GERAN.CELLSTATS4

enter image description here

What are the modification that must be done to execute the query.

Would have been nice to have the query and the table concerned. — David Brabant
– David Brabant, Commented Mar 26, 2012 at 9:47
@Lieven This is remote server can't modify or alter the table — kinkajou
– kinkajou, Commented Mar 26, 2012 at 10:05
@DavidBrabant edited to include query and table screenshot as I can't see the structure — kinkajou
– kinkajou, Commented Mar 26, 2012 at 10:09
@Kitex - You could verify if what is described applies to you. If it does, you are unfortunatly out of luck if you can't modify the table as This behavior is by design. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Mar 26, 2012 at 10:19

Russell Hart · Accepted Answer · 2012-03-26 18:25:34Z

4

Try,

SELECT * 
FROM OPENQUERY(ORACLE, 'select cast(DATETIME_INS as DATE) from U_GERAN.CELLSTATS4')

You can add the other columns to the query once that column works.

answered Mar 26, 2012 at 18:25

Russell Hart

1,85113 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russell Hart Over a year ago

Well first the openquery makes sure that the sql is executed on the remote server. The issue was with the null-ability. So casting to a new data type, before it is sent to the local server makes the meta data explicit and there is no conflict.

Collectives™ on Stack Overflow

In SQL server, how can I query an Oracle Timestamp column over a Linked server connection?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related