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)]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Set_XACT_ABORT_ON]
ON [dbo].[Test_table]
Instead of Update
AS
BEGIN
— 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..
update [PC\NAVDEMO].[90].dbo.[CRONUS$Test_table]
SET Field1 = (select Field1 from INSERTED) ;
END
other example
=============================================
CREATE TRIGGER dbo.SET_XACT_ABORT_ON_INSERT
ON dbo.EJLogItems
Instead of insert
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
insert into [DOMAIN\SQLSERVER].[FgWms].[erp].[Items] (id, recordStatus, recordWritingDate, recordWritingDate, recordImportationDate, plantId, command, item, description, shortDescription, barcode, measurementUnit, stdQuantity, weight, width, depth, height, minimumStock, unitPrice, itemCategory, ABCClass, FIFORangeDays, stdBestBeforeDays, errorMsg)
VALUES ((select id from inserted),(select recordStatus from inserted),(select recordWritingDate from inserted),(select recordImportationDate from inserted),(select plantId from inserted),(select command from inserted),(select item from inserted),(select description from inserted),(select shortDescription from inserted),(select barcode from inserted),(select measurementUnit from inserted),(select stdQuantity from inserted),(select weight from inserted),(select width from inserted),(select depth from inserted),(select height from inserted),(select minimumStock from inserted),(select unitPrice from inserted),(select itemCategory from inserted),(select ABCClass from inserted),(select FIFORangeDays from inserted),(select stdBestBeforeDays from inserted,(select errorMsg from inserted))
END
Thank to Microsoft for support and testing.
