Getting Data from Accounting Package (Linked Server)

If the Accounting Package and doc-link databases are on the same SQL Server

Grant the doc-link default SQL admin account, 'doclink20', at least read-only access to the following accounting package SQL Server objects:

Master database
Controlling database
Each and every company database

If the Accounting Package and doc-link databases are on different SQL Servers (Linked Server)

Add the accounting package Server to the linked server list of the doc-link SQL Server by running the script below (only members of the sysadmin fixed server role can execute this script).

Replace 'Dserver' with the accounting package SQL Server name and 'user1' and 'password' with a username and password that can be used to access the accounting package database. 'user1' has to have at least read-only access to the following accounting package SQL Server objects:

Master database
Controlling database
Each and every company database

DECLARE @sServerName varchar(50)
DECLARE @sUser varchar(25)
DECLARE @sPwd varchar(25)
SET @sServerName ='Dserver' --accounting pkg SQL server name
SET @sUser ='user1' --loginname accounting pkg SQL server
SET @sPwd ='password' --password
EXEC sp_addlinkedserver @server=@sServerName, @srvproduct='SQL Server'
EXEC sp_addlinkedsrvlogin @sServerName, 'FALSE', 'doclink20', @sUser, @sPwd
GO

Make sure the 'Distribution Transaction Coordinator' Service is running on both the ERP application and doc-link SQL Servers.

User Filtering