I was making a linked server connection between SQL Server 2000 and Oracle 8.1.5 (both running on Windows 2000 Server), and I was creating an insert trigger in one of the SQL Server table to insert the same value to the Oracle table. But when I try to make an insert to the table, the following error occurred :
Server: Msg 7391, Level 16, State 1, Procedure <triggername>, Line xx
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8000d048].
I tried to change the trigger into a SELECT statement only and the error still occurred. But when I tried to insert a row directly to the linked server via Query Analyzer, it succeeded.
I have read KB 280106, KB 306212, and KB 329332, and I've tried SET XACT_ABORT ON statement, but none of them can solve the problem.
If there's anyone who has idea on what's wrong or has a suggestion what I should try please help.
Thank you in advance.
Yento,
the reason why the insert using the direct connection to the linked server thru MSDAORA works is, it only requires the oledb provide to support ITransactionLocal. But the trigger case requires ITransactionJoin. I don't know exactly if MSDAORA supports ITransactionJoin or not. (Seems not)
Have you checked MSDTC is running? Perhaps you can also try the Oracle's own oledb provider?
Cheers,
- MC
|||Hi. I've had the same message and the prob was related to inapropriate permissions of the oracle user. It was missing "Force any transaction" permission. Hope this helps.
Re
Joao Neves
|||Hi Joao,
Please let me know the steps of granting "force any transaction" permission to oracle user.
Thanks
Anurag
No comments:
Post a Comment