SQL Server 2008：使用TIMESTAMP（6）列通过链接服务器INSERT INTO到Oracle 11g表

I'm trying to insert a row with TIMESTAMP(6) column in an Oracle 11g table from SQL Server 2008 script, through Linked Server.

我正在尝试通过链接服务器在SQL Server 2008脚本的Oracle 11g表中插入一行TIMESTAMP(6)列。

This is what I tried so far:

这是我到目前为止所尝试的:

INSERT INTO LinkedServer..Schema.Table(TimeStampColumn) 
VALUES(CONVERT(DATE, '2013-08-07'));

INSERT INTO LinkedServer..Schema.Table(TimeStampColumn) 
VALUES(CONVERT(DATETIME, '2013-08-07 12:12:12.000001'));

INSERT INTO LinkedServer..Schema.Table(TimeStampColumn) 
VALUES(CONVERT(TIMESTAMP, '2013-08-07 12:12:12.000001'));

and many combinations, every time I get this error:

和许多组合,每次我收到此错误:

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

链接服务器“LinkedServer”的OLE DB提供程序“OraOLEDB.Oracle”为列“TimeStampColumn”提供了无效的元数据。不支持数据类型。

Is this possible?

这可能吗?

How can I convert SQL Server's varchar or datetime value to Oracle timestamp(6) data type?

如何将SQL Server的varchar或datetime值转换为Oracle时间戳(6)数据类型?

Thanks a lot!

非常感谢!

1 个解决方案

#1

well, i have found it:

好吧,我找到了:

EXECUTE ('begin INSERT INTO TEST_TIMESTAMP(TimeStampColumn)
      VALUES (TO_TIMESTAMP(?,''YYYY-MM-DD HH24:MI:SS.FF6'')); end;', 
      '2013-12-06 11:12:13.123456') 
     AT LINKEDSERVER;

'timestampcolumn' is column with type TIMESTAMP(6)

'timestampcolumn'是TIMESTAMP类型的列(6)

the same way you can use to call oracle functions:Calling an Oracle function from SQL Server Linked Server

与调用oracle函数的方法相同:从SQL Server Linked Server调用Oracle函数

and it also works with variable

它也适用于变量

declare @date datetime2
set @date = SYSDATETIME()
EXECUTE ('begin INSERT INTO TEST_TIMESTAMP(TimeStampColumn)
      VALUES (?); end;', 
      @date) 
     AT LINKEDSERVER;

BUT in this case Oracle truncates it to seconds

但在这种情况下,Oracle会将其截断为秒

#1