SQL Server Performance

Unable to start a nested Transaction

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by ramyasundaram2002, Apr 14, 2009.

  1. ramyasundaram2002 New Member

    We are trying to update a table on Server A from Server B. But we get the following error
    "Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked Server "A". A nested transaction was required because the XACT_ABORT option was set to OFF"
    We have tried to use XACT_ABORT ON and distributed transaction statements before the update. On using this, the update statements does say '1 row updated' but when we select that row again, the update statement has not committed.
    MSDTC is enabled. Both servers are sql server 2005.
    Can someone suggest any solution?
  2. MohammedU New Member

    Did you try..."begin distributed tran"? if not try it... set xact_abort on
    begin distributed tran
    update ....
    commit tran
    set xact_abort off
  3. ramyasundaram2002 New Member

    OK I found a solution. I'm not sure why this exactly caused an issue in the first place but here is what I did.
    That table had a primary key which was a non-clustered index. I dropped the primary key and recreated it with a clustered index. Once I did this, I was able to perform the update without issues.
  4. dhamu294 New Member

    Hi Ramyasundaram
    by default primary key created the uniue clusterd index. so it should not be the issue.
    try using update with in transaction if no error commit it else roll back all the transaction.
    get back in case of any doubts.

Share This Page