4

I have tried some examples but so far not working. I have a Link Server (SQL Server 2014) to an Oracle 12C Database.

The table contain a datatype TIMESTAMP with data like this:

22-MAR-15 04.18.24.144789000 PM

When attempting to query this table in SQL Server 2014 via link server I get the following error using this code:

SELECT CAST(OracleTimeStampColumn AS DATETIME2(7)) FROM linkServerTable

Error:

Msg 7354, Level 16, State 1, Line 8
The OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer" supplied invalid metadata for column "MyDateColumn". The data type is not supported.

While the error is self explanatory, I am not certain how to resolve this.

I need to convert the timestamp to datetime2. Is this possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Asynchronous
  • 3,917
  • 19
  • 62
  • 96
  • Maybe these links will help: [1](http://stackoverflow.com/questions/9869892/sql-server-to-oracle-linked-server-query-error), [2](http://drewstechnotes.blogspot.com/2010/05/easy-workaround-for-oracle-linked.html). – Ponder Stibbons Mar 22 '15 at 22:04

2 Answers2

6

You can work around this problem by using OPENQUERY. For me, connecting to Oracle 12 from SQL 2008 over a linked server, this query fails:

SELECT TOP 10 TimestampField
FROM ORACLE..Schema.TableName

...with this error:

The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" supplied invalid metadata for column "TimestampField". The data type is not supported.

This occurs even if I do not include the offending column (which is of type TIMESTAMP(6). Explicitly casting it to DATETIME does not help either.

However, this works:

SELECT * FROM OPENQUERY(ORACLE, 'SELECT "TimestampField" FROM SchemaName.TableName WHERE ROWNUM <= 10')

...and the data returned flows nicely into a DATETIME2() field.

3

One way to solve the problem is to create a view in oracle server and convert the OracleTimeStampColumn compatible with sql server's datetime2datatype. You can change the time format to 24 hours format in oracle server's view and mark the field as varchar. Then you can convert the varchar2 column to datetime2 when selecting the column in SQL Server.

In Oracle Server

Create or Replace View VW_YourTableName As 
select to_char(OracleTimeStampColumn , 'DD/MM/YYYY HH24:MI:SS.FF')      OracleTimeStampColumn from YourTableName

In SQL Server

SELECT CAST(OracleTimeStampColumn AS DATETIME2(7)) FROM **linkServerVIEW**
Sukanya1991
  • 778
  • 3
  • 17