I have a few triggers on a SQL2000 table that does inserts to other servers via linked servers. 2 of the linked servers are SQL7 while the other is an Informix Dynamic Server 9.3. All three triggers fire together due to an insert on the SQL2000 table. I use a insert query to fire these triggers via query analyser. When I run the insert the first time it errors with the following...
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
This only happens with the trigger that points to the Informix Server. However when I run the insert the second time it runs without errors. Does anyone know why this occurs?? And how can I fix this??
The linked Server to the the Informix Server is fine as I can do select queries to it via query analyser.
Is there some sort of setting that needs to be invoked??