Unanswered: problem accessing remote server from within a transaction
I am facing the following problem:
I have a delete trigger on a table, say TA, on a server A at which server B is registered as a remote server. Inside the trigger, I have
select @count = B.<dbname>.dbo.TB inner join deleted on
(<some condition involving both the tables>) where TB is a table
on server B.
if(@count > 0)
endOn deleting a row from table A I get the following message :
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. ] .
I tried to replicate the problem in query analyzer and I found that
whenever I execute something like 'select * from B.<dbname>.dbo.TB'
inside a begin tran - commit tran pair, I get the problem