I'm trying to insert into a table variable from an EXECUTE(...) AT ORACLE_LINKED_SERVER statement but frequently I get one of the following errors:
Msg 1206, Level 18, State 124, Line xxx
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
Msg 8520, Level 16, State 1, Line xxx
Internal Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed to commit: 0x8004d019(failed to retrieve text for this error. Reason: 15105).
Msg 3930, Level 16, State 1, Line xxx
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
However, sometimes it works without error.
Apart from these errors, I don't completely understand why this is being treated as a distributed transaction because I'm inserting into a T-SQL table variable and I thought these were exempt from the usual transaction rules (apologies for my ignorance!).
I'd much prefer it if I could flag it as not being part of a transaction.
I've spent quite a bit of time with Google, MSDN, and OTN but can't spot anything which appears to match my particular problem so would appreciate any thoughts or help please.
Code running on MS SQL 2008 Enterprise Edition (64-bit) 10.0.2531.0 SP1 on W
View Complete Post