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
begin distributed tran
select @count = count(deleted.*) from 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)
On deleting a row from table TA, 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. But removing
tran statements from the trigger also does not solve the problem.
I also tried setting the 'enforce distributed transactions' property of both the servers from enterprise manager(I am not sure what difference it is supposed to make), but it did not help either.