Folks i've two SQL SERVER, SERVER1 and SERVER2. I am updating a table at SERVER1 by joining a table with SERVER2. The query works fine, but update fails.
FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
I get the following error:
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].