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, Mar 26 '12 at 09:47
Did you read [this](http://support.microsoft.com/kb/251238)? — Lieven Keersmaekers, Mar 26 '12 at 09:49
@Lieven This is remote server can't modify or alter the table — kinkajou, Mar 26 '12 at 10:05
@DavidBrabant edited to include query and table screenshot as I can't see the structure — kinkajou, Mar 26 '12 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, Mar 26 '12 at 10:19

score 4 · Accepted Answer · answered Mar 26 '12 at 18:25

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 '12 at 18:25

Russell Hart

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. – Russell Hart Mar 27 '12 at 17:24

1 Answers1