XACT_ABORT “OFF” using NAV linked views on linked servers

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).

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