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?
Did you try..."begin distributed tran"? if not try it... set xact_abort on begin distributed tran update ....commit tran set xact_abort off
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.
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.