July 26, 2021 at 4:53 pm
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?
July 26, 2021 at 4:58 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 26, 2021 at 6:26 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply