Insert Oracle Linked Server timestamp error...

I'm trying to insert into an Oracle table, setup via a linked server.
Everything works, but I receive an error during the insert, the
statement and error are below:
INSERT INTO ORALINKSRV..FIN.FILE_XFER_STATUS_DETAIL
(FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_
ID,FILE_NM,
FILE_PROC_DTTM FILE_SUFFIX_ID)
Select FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_I
D,FILE_NM,
FILE_PROC_DTTM,FILE_SUFFIX_ID
From Temp_FIN_Detail
ERROR:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
'FILE_PROC_DTTM'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'FILE_PROC_DTTM'
(ordinal 5) of object '"FIN"."FILE_XFER_STATUS_DETAIL"' reported an
unsupported value for DBTYPE of 13].
We've updated to the latest Oracle drivers on the server. We've
switched between the Oracle and MS OleDB Providers.
Any work arounds appreciated...
Thanks
bobAdditional information.
This is Oracle 9.203.
Oracle introduced a new data type timestamp, this data type does not
seem to be updateable or insert via SQL Server linked server.
I'd appreciate if anyone else could test and confirm this. We've
contacted MS Support and they weren't very helpful, indicating we were
the only ones who have reported a problem, and that is is most likely
an Oracle problem.
I'm just looking for some guidance so we can establish a standard to
not use the Oracle time stamp data type on tables that require data to
be transferred back and forth between SQL Server and Oracle.
Thanks
bob|||Try using Openquery and wrapping the value in to_timestamp.
You should also check your nls timestamp format on the
Oracle server.
-Sue
On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
Horkay) wrote:

>Additional information.
>This is Oracle 9.203.
>Oracle introduced a new data type timestamp, this data type does not
>seem to be updateable or insert via SQL Server linked server.
>I'd appreciate if anyone else could test and confirm this. We've
>contacted MS Support and they weren't very helpful, indicating we were
>the only ones who have reported a problem, and that is is most likely
>an Oracle problem.
>I'm just looking for some guidance so we can establish a standard to
>not use the Oracle time stamp data type on tables that require data to
>be transferred back and forth between SQL Server and Oracle.
>Thanks
>bob|||Sue,
The query can be made to work for openquery with a to_char() statement,
unfortunately this does not work for Inserts.
I finally did receive a response from Microsoft support, the timestamp data
type is not supported, there is no plan to support it. They suggested using
the .net managed providers for Oracle, which of course can not be used for
linked servers.
They referenced several kb articles about data types supported and not
supported in Oracle, but basically the timestamp, clobs and blobs were out
( i expected the blobs but was surprised by the timestamp).
Anyway at least I know Microsofts position on it's ole db provider, they
support it, but have no plans to enhance it, watch thos data types
introduced from Oracle 8 on...
Bob
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:04snb01147cfcpn394k2ufrrv9jsr953u9@.
4ax.com...
> Try using Openquery and wrapping the value in to_timestamp.
> You should also check your nls timestamp format on the
> Oracle server.
> -Sue
> On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
> Horkay) wrote:
>
>