SQL Server Performance

View trigger - Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'

Discussion in 'General Developer Questions' started by Naseem25jun, Feb 6, 2011.

  1. Naseem25jun New Member

    Hi All, I’ve a view which its data comes from linked server. I need to write Insert/Delete/update trigger on my view to reflect the changes into the same linked server which the data have loaded from. When the triggers run I get this error. [OLE/DB provider returned message: Cannot start more transactions on this session.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].Msg 7395, Level 16, State 2, Line 1Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required because the XACT_ABORT option was set to OFF. As far as I have searched I need to set “SET XACT_ABORT ON” At top of the triggers. By using XACT_ABORT ON, the insert trigger working fine ,but still I’m getting the above error when delete or update happening. I appreciate any helps. Thank you so much ,
  2. satya Moderator

    Welcome to the forums.
    The best bet is to check whether MSDTC service is up and running first.
  3. Naseem25jun New Member

    Hi Satya,
    Thank you for your answer.
    The MSDTC is running in both servers .
    I was thinking maybe the bellow steps caused the issue.
    1. The data in the view loads from linkedserver (LS1)
    2. There is update trigger on view
    3. When update happening ,it's going to update the data in the linked server (LS1)
    The strange part is that , even though I removed update linked server data from update trigger , I still get that error during updating view.
    Thank you again,
    Kind Regards,
    Naseem
  4. satya Moderator

    Check the setting for "Enforce Distributed Transactions" and also SET XACT_ABORT ON before the update of VIEW.

Share This Page