I have a trigger that is trying to take some data from an inserted record, and insert it into a table on a database that is on another server (same network). All servers are MS SQL 7. When I try to insert a record, and activate the trigger I receive the following message:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]
I have linked the servers with both SQLOLEDB and MSDASQL providers and can see both with no problem in Enteprise Manager. However I get the above error when the trigger activates. I also use BEGIN DISTRIBUTED TRANSACTION before my insert but its no good. I believe this is more of a configuration issue. I can run an insert from Query Analyser inserting to a different Server.
Is there anything I need to configure or get running such as MSDTC. If so, how do run MSDTC? What are the steps in setting it up?? How do I know its running??
Can anyone help with this? I need to know what I need to do so that I can send these transactions from one server to another.