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!

did you try just passing string '2013-12-25 12:12:12' for example (without converting) ? — sqladmin
– sqladmin, Commented Jul 9, 2013 at 1:33
thanks @sqladmin.. yes, same error message. Your suggestion works if the oracle column's data type is Date. — vladiastudillo
– vladiastudillo, Commented Jul 9, 2013 at 2:24

Community · Accepted Answer · 2017-05-23 10:34:08Z

3

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

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 9, 2013 at 8:40

sqladmin

2,2092 gold badges17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related