Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Unanswered: problem accessing remote server from within a transaction

    Hi,

    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
    code like

    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)
    begin
    rollback tran
    raiserror
    return
    end.
    commit tran

    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.

  2. #2
    Join Date
    Nov 2002
    Posts
    14

    Re: problem accessing remote server from within a transaction

    Triggers are always invoked as part of a transaction so you can eventually roll it back. This is why removing the tran statements from the trigger does not change anything.

    I cannot answer the other question - why you cannot select from a remote server while being in a local transaction. I'll think about it.
    Zlatko Michailov
    Z-SQL

  3. #3
    Join Date
    Nov 2002
    Posts
    14

    Re: problem accessing remote server from within a transaction

    Since you are only reading from the second server, you don't need distributed transactions at all. Set all transaction properties back to local on both servers and see what happens.

    And get rid of the 'begin tran' statement in the trigger.
    Zlatko Michailov
    Z-SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •