XACT_ABORT “OFF” using NAV linked views on linked servers
I describe this scenario because it’s interesting: NAV with linked views based on linked servers (distributed views). In my scenario i can’t change SQL Server Default XACT_ABORT option, we have two differents configurations for both SQL Servers involved in linked views.
The issue is related to a table in NAV linked to a view on an external table, made available through a linked server in SQL.
When trying to save modifications in the table, the following error message is received:
“Unable to start a nested transaction for OLE DB Provider. A nested transaction was required because the XACT_ABORT option was set to OFF. OLE DB Provider returned message: “Cannot start more transactions on this session””.
With the XACT_ABORT parameter enabled data can immediately be written into the other database on a SQL server 2016. Therefore the SQL option XACT ABORT should be set to true, but when this is the case, NAV is crashing when inserting, modifying or deleting a record in every table.
SolutionWorkaround Suggested by Microsoft
It appears that the cause of our issue is the fact that the parameter needs to be enabled in the same transaction as the statement that is running. A simple workaround is to create 3 “Instead of” triggers, which will enable the parameter before any Insert, Update or Delete statement, as below:
Example of the Update trigger
— “For Update” Trigger
USE [Demo Database NAV (9-0)]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [dbo].[Set_XACT_ABORT_ON]
Instead of Update
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
–Change this below line of code depending on your requirements..
SET Field1 = (select Field1 from INSERTED) ;
Thank to Microsoft for support and testing.