Distributed Transactions ?

Hi There

I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.

BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.

I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.

When i execute the update or insert i get the error:
Cannot start a distributed transaction.

I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.

Can anyone please reccomend a good link as to how to get distributed transactions to work.

Thanx
P.S I have already checked this link http://support.microsoft.com/?kbid=816701.

Also i have found references to checking avrious services are running etc, seems there is alot to setup to get MSDTC to work but i cannot find the setup in BOL, not sure everything i have to do to egt MSDTC to work ?

Please help Thanx
|||

Have you tried setting XACT_ABORT ton ON?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Hi

Tried this

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
UPDATE PSAPMSGSUBCON SET STATUSSTRING = 'ERROR'

Got this error:

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Procedure PSAPMSGSUBCON_MESSAGE_ERROR_TRIGGER, Line 11
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

ALso found this in BOL:

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.

I think i am simply missing something to ensure MSDTC is setup and running correctly.
Is the only thing require for MSDTC to work , that it be running, servers have the network DTC enabled and you stipulate

BEGIN DISTRIBUTED TRANSACTION

?