SQL Server 2008: INSERT INTO through Linked Server to Oracle 11g table with TIMESTAMP(6) column

Question

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

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.

Is this possible?

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

Thanks a lot!

Answer 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)

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

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