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