Reading Data from Another SQL Database such as Oracle

If you want to see data from an external SQL-based application inside your NAV system, this can be done very easily without the messy process of exporting and importing data into NAV.

And when we say SQL, this means any database including SQL Server, Oracle, Access or MYSQL

The key requirement is that you are able to create a VIEW from Microsoft SQL Server Management Studio to this external database by creating a Linked Server, most likely using SQLOLEDB.  Of course, if the data is another SQL Server database, then just access that server directly from your list of servers.

Screen Shot 2015-02-26 at 2.00.42 PM

Once you have connected to the external database using a Linked Server, then create a view in SQL Server to this database to access as an example their employee table.


CREATE VIEW ExternalDataViewName
AS
SELECT FirstName, MiddleName, LastName, City
FROM LinkedServer.dbo.Employee
GO

Then create a new table in the NAV Object Designer referencing this view name and the columns selected in the view, making sure you set its Linked Object property to Yes.

Screen Shot 2015-02-26 at 2.20.45 PM

 

Once this is done, you can use this table just like any NAV table including the ability to add flowfields from other tables to this table.