error when using date column with remote oracle table

  • I realize this might be out of scope for the forum but figured it's worth a shot...

    I have a process where I copy data from a table in our local SQL Server instance to a remote Oracle table via Linked Server.  I can do the INSERT without any issue at all.

    I can also DELETE from the remote Oracle table - so I know I have permission to do so.  The problem is if I try to use a date filter on the DELETE command - for example:

    delete from [REMOTESERVER.COMPANY.COM:1234]..[DW_STG_OWNER].[FACT_BOOKING_INCR_STG]
    where INCREMENTAL_RUNTIME = '2021-07-26 11:06:00.0000000'

    Throws the following error:

    OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER.COMPANY.COM:1234" returned message "The system cannot find message text for message number 0x80040e21 in the message file for OraOLEDB.".
    Msg 7345, Level 16, State 1, Line 1
    The OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER.COMPANY.COM:1234" could not delete from table ""DW_STG_OWNER"."FACT_BOOKING_INCR_STG"". There was a recoverable, provider-specific error, such as an RPC failure.

    As best I can find, the error code 0x80040e21 indicates a data type mismatch.  I believe the data type on their end is DATETIME is there a way I can cast the dates on my end to avoid this error?

     

  • By default, I think Oracle timestamp only has 6 digit precision for sub-seconds (FULL DISCLOSURE: I haven't been an Oracle DBA since Oracle 8).  Try cutting the sub-seconds from 7 decimal places to 6 (or less).  Or change the timestamp on Oracle to 7 digits of sub-second precision.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • You might be able to use OPENQUERY instead and force the date into a format Oracle likes, So something like

     

    DELETE FROM OPENQUERY([REMOTESERVER.COMPANY.COM:1234], 'SELECT * FROM [DW_STG_OWNER].[FACT_BOOKING_INCR_STG] WHERE INCREMENTAL_RUNTIME = TO_DATE('2021-07-26 11:06:00', 'YYYY-MM-DD HH24:MI:SS')')

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply