通过链接服务器连接时，如何将Oracle TIMESTAMP数据类型转换为SQL Server DATETIME2数据类型? [英] How do I convert an Oracle TIMESTAMP data type to SQL Server DATETIME2 data type while connected via Link Server.?

问题描述

我已经尝试了一些示例，但到目前为止没有用. 我有一个Link Server (SQL Server 2014)到Oracle 12C Database.

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

该表包含数据类型为TIMESTAMP的数据，如下所示:

The table contain a datatype TIMESTAMP with data like this:

22-MAR-15 04.18.24.144789000 PM

当尝试通过链接服务器在SQL Server 2014中查询此表时，使用此代码出现以下错误:

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

错误:

7354消息，状态16，状态1，第8行
链接服务器"MyLinkServer"的OLE DB提供程序"OraOLEDB.Oracle"为列"MyDateColumn"提供了无效的元数据.不支持该数据类型.

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.

我需要将timestamp转换为datetime2.这可能吗?

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

推荐答案

您可以使用OPENQUERY解决此问题.对我而言，通过链接服务器从SQL 2008连接到Oracle 12时，此查询失败:

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

...出现此错误:

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

即使我不包括有问题的列(类型为TIMESTAMP(6)，也会发生这种情况.将其显式转换为DATETIME也无济于事.

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.

但是，这可行:

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

...并且返回的数据很好地流入了DATETIME2()字段.

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

这篇关于通过链接服务器连接时，如何将Oracle TIMESTAMP数据类型转换为SQL Server DATETIME2数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！