Sage 100 – How to set up a Linked Server in SQL Server Management Studio
Products
Sage 100
Description
How to set up a Linked Server in SQL Server Management Studio to pull data from Sage 100 (formerly MAS90/MAS200) data files
Disclaimer
Backup Warning
Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.
Network Warning
This solution requires advanced knowledge of your network. Contact your system administrator for assistance. Modifying Windows security incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows security. Always create a backup of your data before proceeding with advanced solutions.
Operating System Warning
This solution requires advanced knowledge of your computer’s operating system. Contact your system administrator for assistance. Modifying your Windows Registry incorrectly can severely affect system operations. Sage is not responsible for operation issues caused by incorrectly modifying your Windows Registry. Always create a backup of your data before proceeding with advanced solutions.
Support
Sage Customer Support does not provide assistance for issues related to third party products or enhancements, hardware, report customizations, state or federal tax-related questions, or specific accounting questions. Please contact your Sage business partner, network administrator, or accountant for assistance. Please review this document for additional information on the scope of Sage Customer Support Services.
Resolution
Note: Sage Customer Support is not responsible for assisting with the configuration of a linked server. Consult a SQL Database Administrator for assistance.
Setup steps for a SQL Server Linked Server to pull data from Sage 100 ERP data files. Three components for the Linked Server settings:
- ODBC driver
- Connection String
- Link Server settings
ODBC driver:
Run workstation setup from the MAS90\wksetup folder. This places the DSN driver to be used by the SQL Server Linked Server data source field.
Create a new System DSN (silent ODBC connection) using the “MAS 90 4.0 ODBC driver”.
- Do not use SOTAMAS90 as this is reserved connection.
- Make a note of your new DSN name as it is needed below in Linked Server configuration.
The fields to be used with the ODBC connection are:
- Basic Tab, “Database directory location” = path to the \MAS90\ folder on Sage 100 Server
- Logon Tab, “Company code” = 3 digit Sage MAS 90 company code
- Logon Tab, “Default User ID” = MAS90 user ID
- Logon Tab, “Password” = MAS90 password if used
- Debug Tab, Select Apply.
- Debug Tab, Select Test Connection to confirm connection, it should return a number of tables found.
- Debug Tab, Select Connection String and copy results to Notepad (you will need in Linked Server configuration).
- Select OK
For more information, see Related Resources below, for How to create a silent connection to an ODBC data source.
Connection string:
From the ODBC System DSN created above, select the Debug Tab, select the Connection String button. This is copied to the “Provider string” in step 4 below.
Link Server:
Setup new Linked Server in SQL with the following settings:
- Provider= Microsoft OLE DB Provider for ODBC Drivers
- Product Name= Name of System DSN from above
- Data Source= Name of System DSN from above
- Provider String= Connection string copied from new DSN created above
Click OK.
Linked Server settings are complete.
You should be able to expand the Linked Server entry in SQL and the Catalogs list should show the tables from Sage 100.
Next step is to test from New Query in SQL:
Select * from openquery ([LINKED_SERVER_NAME], ‘select * from gl_account’)
Rows of data will return in the Query results screen.