Often there is requirement to get data from external sources. We all know about dataports (now xmlports), webservices, and even FILE.READ function. But sometimes there is more convenient way to get data from an external SQL server.
Since old times we do have ‘LinkedObject’ property on tables. What is it? It is just a property, which allow you to link a NAV table to SQL view in the same database. The field definitions in the view and NAV table should be the same, and NAV table name must be exactly the same as view name (but without company name). Ok, this give us a convenient way to access data on same server. What about external servers?
Quite simple: since sql 2005 we can link SQL server to another SQL server (and not only to MS SQL!).
So when we link sql server A and sql server B then we can access data from server B within server A. So we can create a vien on server A, which will read data from server B and then create an NAV linked table, attached to this view…
How do we do this?
On the server A run the following query:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N’B’,
@srvproduct=N’SQL Server’ ;
GO

Then sort out security

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N’B’,
@locallogin = NULL ,
@useself = N’True’ ;
GO

Then test connection

SELECT name FROM [A].master.sys.databases ;
GO

Then we can do something like this:

SELECT local.name AS Logins, linked.name AS LinkLogins
FROM master.sys.server_principals AS local
LEFT JOIN [B].master.sys.server_principals AS linked
ON local.name = linked.name ;
GO

If this worked – you are able to read server B data from server A.