I use linked server from mssql to oracle, it work fine.
but when i use linked server in trigger, it's error (MSDTC is unavailable).
what i should do to let trigger can connect linked server, or there is no way to do. thx in advances.
it's seems like trigger doen't allow linked server
i've try run query to call linked server outside trigger, it still work fine.
i used MSSQL2000 on XP sp1, oracle 9i.
Code:
--/ error message was shown following
Server: Msg 8501, Level 16, State 3, Procedure _z2, Line 25
MSDTC on server 'zzz' is unavailable.
--/ procedure inside trigger (trigger will call this procedure)
--/ use for test whether can select data from linked server?
ALTER PROCEDURE _z2
AS
set xact_abort on --/ no matter set xact on or not, it still can't connect linked server
IF EXISTS
(
SELECT *
FROM
OPENDATASOURCE(
'MSDAORA',
'Data Source=zzz;User ID=sa;Password=passwd'
)..SA.TB_COATZ
)
BEGIN
RAISERROR('~~~>___<~~~.', 16, 1) --/ if can connect, print out this msg
RETURN
END
set xact_abort off