Trigger and MSDTC issue
Posted on 2006-11-16
I have a very simple Trigger on a high traffic indexed table on a SQL Server 2000 Enterprise server.
The trigger goes like this:
CREATE TRIGGER tr_AnswerBankOrdersInserted
INSERT INTO AnswerBankOrdersInserted (fkUserOrder, iRevision)
Select Distinct fkUserOrder, iRevision from Inserted
All it's supposed to do is to grab 2 of the 6 columns in the "AnswerBank" table and "ON INSERT" insert them into a
new table called AnswerBankOrdersInserted.
This trigger works perfectly when I try to insert data with a Select Into query or any other simple direct query,
however it fails when I try to Insert data from a stored procedure.
I get the following error:
[SQL Server]MSDTC on server 'XXXXXX' is unavailable.
I get this error when I try to execute the stored procedure from a web page.
I'm still not 100% sure that the issue is from the trigger, however thats the only thing I can point to at this moment.
Thanks for any help.