Unanswered: DISTRIBUTED TRANSACTION gives off ITransactionJoin error when using SQLOLEDB
I've been trying to encapsulate this code into a transaction, but I get this error when I try to run it...
SET XACT_ABORT ON
BEGIN TRANSACTION -- Also tried as a DISTRIBUTED TRAN
INSERT INTO item
SELECT [field1], [field2]
If @@ERROR <> 0
SET XACT_ABORT OFF
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[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].
I checked the servername properties for both servers and they do have names. I will be trying to get the firewall setup for the correct ports to be used with DTC now, but I know this will take a day or two since this i a dedicated server we are using at a hosting provider. So I will let you know how it goes...