-
linked server in trigger?
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
-
Start MS DTC on your sql server.
-
You should not use OPENDATASOURCE or OPENROWSET in triggers. If you are going to access a remote server this frequently, you should create a linked server. OPENDATASOURCE and OPENROWSET have a much higher performance overhead than a linked server.
You can perform the same check by using OPENQUERY and a linked server.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|