Linking to Non Navison SQL table

We are looking to implement an update table from another application, so that when a change is made in one system we write that change back to Navision. We are going to have an update table in SQL either in a separate db or in the Navision db. I know of two ways of doing this, one through an ODBC connection and an another through creating a SQL table and then creating the same in Navision and linking it. Are there any other ways of doing it? From the two ways I have has anyone found a particular way to be more efficient. Thanks Kevin

caution needs to be exercised before resorting to excahge of data between tables from one application to another. In fact problem is not just that of data transfer between 2 apps in real time but also of maintaining the integrity of data. Imagine you had done tranfer from external app to GL entry table and sum of the amounts in the newly added records dont match. Its like pulling the whole system down.

Kevin, You can create a view that will be linked to an outside table, that Navision will treat as its own table. There are some twists though: 1. Commit/Rollback - you have no control over that in the linked table. 2. Indexing - external views may not always be indexed. If the table is in the other DB or server, you will not be able to create a foreign index. 3. Backup /restore – ouch. 4. Re-design of the linked tables - double ouch. Hope that helps. Alex

You don’t have to use a view (if you’re using a SQL Server database). 1. Create a table in Navision 2. Define a trigger on the external table that updates the Navision table whenever the original table is modified. This is straightforward if the the external table is on the same server as the Navision database. If it’s not then it can be accomplished by defining a linked server.

By default a linked object is under transaction control so commit and rollback work as expected. Only if you set the LinkedInTransaction property to No will changes be done outside of your Attain transaction. This is necessary if the object you are linking to is outside the current database. You can link to a table or view. A view cannot be redesigned from within Attain, but if your external system ‘owns’ the tables on which the view is based you probably dont want to do that anyway. Backup/restore - dont see the problem provided your table/view object exists in the database when restoring. The data within a linked object is not backed up by Attain. All in all the LinkedObject idea provides good integration between Attain and SQL Server.