Hello!
I have an Oracle linked server connected through MSDAORA. Linked server
queries work perfectly - the "openquery" ones as well as the
4-part-named ones.
The problem I have is with embedding the queries within SQL Server
triggers.
Trigger:
CREATE TRIGGER tgTest ON [dbo].[test]
FOR INSERT, UPDATE, DELETE
AS
select * from openquery(LS, 'select * from ORACLE_TEST')
executing "delete from test" in SQL Query Analyzer raises this error:
Server: Msg 7391, Level 16, State 1, Procedure tgTest, Line 5
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 0x8004d01b].
I've tried almost every solution I found online, but nothing helped:(
This looked promissing: http://tinyurl.com/nk2wd , but it didn't get me
any futher.
Maybe someone can get me through the troubleshoot mentioned in that
link:
- check if DTC running properly
How do I check that? If I open the "Support services" in Enterprise
Manager and right-click the "Distributed Transaction Coordinatior" I
can stop the service, what indicates the service is running, but is
there anything else I should check? I have 0 items in the right window
pane of the DTC item, is it OK?
- registry setting as discussed earlier
The following Registry Keys should be entered:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
My entries are:
"OracleOciLib"="ociw32.dll"
"OracleSqlLib"="SQLLib18.dll"
"OracleXaLib"="xa73.dll"
Are they OK?
- check if Mtxoci.dll is loaded
There is a Mtxoci.dll in my system32 dir, but how do I tell if it's
loaded? Should I regsvr32 it?
- SET XACT_ABORT ON should be use in your SQL statement, for example:
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
SELECT statement
COMMIT TRAN
I've tried that, both in trigger but also surrounding the query that
fires the trigger.
Am getting deseperate - please help. Will send candies!
TIA
