[SOLVED]Could not find server AAA in Sys.Server - Linked Servers View - XACT_ABORT option

Tomigs

Hi all,

We are facing an issue with a NAV(NAV2015) set up that has some views connected to Linked Server: after having migrated the databases of the linked server to a new server, there is one particular process that errors when we try to update data from the linked server, making reference to the old linked server. Reading data from it seems to be ok though. Any suggestion is welcome.

Scenario:

Years ago we set up SQL Linked Servers connected to a NAV database so that we can see as views the information of certain tables on the linked database, and import some sales information from that database, that we then process into the NAV database.

Recently the databases that were in the old Linked Server have been migrated from the old server (AAA) to a new one (BBB).

On the NAV SQL server, we have removed the old linked server and rebuilt the linked server to point to the new server and all looks ok: I can run the views from SQL and from NAV and presents the information.

However, if I try to modify the data on the view, either by code or manually, opening the view in NAV, we get an error: "The following SQL error was unexpected. Could not find server AAA in sys.servers. Verify that the correct Server Name was specified..."

So it is somehow making reference to the old AAA server somewhere, but we haven't been able to identify where. I get the impression that it is some sql/service/configuration or NAV object property that is not updated or refreshed but I'm not sure where.
Looks like it is reading from the new (BBB) server but it is trying to update the old (AAA) server.


What we have done:
- New SQL Server BBB: MSDTC enabled.
- NAV SQL Server: SQL: new Linked server configured and old Linked Server
- NAV SQL Server: SQL views for SC updated.
- NAV SQL Server: SQL stored procedures for SC updated.
- NAV App Server: NAV SC objects reviewed ok, recompiled all ok.
- NAV App Server: SC View tables tested/opened ok from within the NAV Dev Env.


Thanks,
Tomas

Tomigs

Hi again,

Fingers crossed, this issue is fixed.

The issue and resolution had to do with Roberto's link:
https://robertostefanettinavblog.com/2017/06/02/xact_abort-off-using-nav-linked-views-on-linked-servers/

There were some triggers associated to the SQL Views that needed to be updated. Adapting them towards the new server has worked.

So many of the SQL views had its own trigger that needed to be readapted.




Regards,
Tomas

Tomigs

Hi again,

As often happens, I was doing some extra tests while writing this post, and may have found something:

I figured that probably the way I altered the definition of the views was incomplete, resulting in the "view" option working ok, but the "modify" option keeps pointing to the old server.

I have completely dropped and re-created one of the views. Now I get a different error when I try to update the data from the view. It looks a bit better because it points to the new server BBB now.

"Unable to start a nested transaction for OLE db provider "SQLNCLI11" for Linked Server "BBB". A nested transaction was required because XACT_ABORT option was set to off."



Now I'm investigating if turning off the XAC_ABORT option would be the solution and how to do it.
https://robertostefanettinavblog.com/2017/06/02/xact_abort-off-using-nav-linked-views-on-linked-servers/

Again, any suggestion is welcome.

Thanks
Tomas

Tomigs

Hi again,

Fingers crossed, this issue is fixed.

The issue and resolution had to do with Roberto's link:
https://robertostefanettinavblog.com/2017/06/02/xact_abort-off-using-nav-linked-views-on-linked-servers/

There were some triggers associated to the SQL Views that needed to be updated. Adapting them towards the new server has worked.

So many of the SQL views had its own trigger that needed to be readapted.




Regards,
Tomas