How to query Sage 100 with SQL through the SOTAMAS90 ODBC driver

1. Pick the right connection

A Sage employee on the Sage community recommends that third-party programs do not use SOTAMAS90 itself, because Sage 100 recreates that DSN each time it starts. Use a DSN-less connection string, or create your own "silent" DSN with the company and user filled in:

Driver={MAS 90 4.0 ODBC Driver}; Company=ABC; UID=JSMITH; PWD=secret;
Directory=C:\Sage\Sage 100 Standard\MAS90; CacheSize=4; DirtyReads=1;
BurstMode=1; StripTrailingSpaces=1

• The braces around the driver name are required.
• Directory points at the MAS90 folder of the Sage 100 installation (a local path or a UNC share).
• Company is the three-character company code. Enter the company code and the user ID in upper case.
• StripTrailingSpaces=1 saves you from trimming fixed-width fields in your code.

32-bit or 64-bit

Your program and the driver must have the same bitness. A mismatch is the most common reason a connection fails with "data source name not found". Sage 100 2026 is 64-bit only, so new integrations should use the 64-bit driver. On older versions, the 32-bit DSNs are managed with C:\Windows\SysWOW64\odbcad32.exe.

2. Connect from Python

import pyodbc

conn = pyodbc.connect(
    "Driver={MAS 90 4.0 ODBC Driver};Company=ABC;UID=JSMITH;PWD=secret;"
    r"Directory=\\sage-server\Sage\Sage 100 Standard\MAS90;"
    "StripTrailingSpaces=1",
    autocommit=True,
)
cur = conn.cursor()
cur.execute("SELECT CustomerNo, CustomerName FROM AR_Customer WHERE ARDivisionNo = '01'")
for customer_no, name in cur.fetchall():
    print(customer_no, name)

If you created a silent DSN, the connection string is only "DSN=SOTAMAS90_SILENT". The same strings work from .NET (OdbcConnection), PHP (odbc_connect) and Excel.

3. The ProvideX SQL dialect

The driver speaks an older ODBC SQL dialect. Four rules save most of the debugging time:

• Date literals use ODBC escapes: WHERE OrderDate >= {d '2026-09-01'}.
• Joins: the INNER JOIN keyword fails on some versions. A comma join with the condition in WHERE works. For outer joins use the escape {OJ SO_SalesOrderHeader h LEFT OUTER JOIN SO_SalesOrderDetail d ON h.SalesOrderNo = d.SalesOrderNo}, which also made a slow three-table query fast in one Stack Overflow answer.
• Filter on key columns. The driver can seek on a table's key (for example SalesOrderNo, or ARDivisionNo + CustomerNo). A filter on a non-key column reads the whole file.
• Invalid dates in old data can break a query. Wrap the column: {fn convert(OrderDate, SQL_VARCHAR)}.

4. Queries you will need

Customers with an open balance

SELECT ARDivisionNo, CustomerNo, CustomerName, EmailAddress,
       CreditLimit, CurrentBalance
FROM AR_Customer
WHERE CurrentBalance > 0

Open sales orders with their lines

SELECT h.SalesOrderNo, h.OrderDate, h.ARDivisionNo, h.CustomerNo,
       h.CustomerPONo, d.LineKey, d.ItemCode, d.QuantityOrdered,
       d.QuantityShipped, d.UnitPrice, d.ExtensionAmt
FROM SO_SalesOrderHeader h, SO_SalesOrderDetail d
WHERE h.SalesOrderNo = d.SalesOrderNo
  AND h.OrderStatus IN ('N', 'O')
  AND h.OrderType = 'S'

OrderStatus is N (new), O (open), H (hold) or C (closed). OrderType S is a standard order; Q is a quote, B a back order, M master, R repeating.

Stock per warehouse for one item

SELECT ItemCode, WarehouseCode, QuantityOnHand,
       QuantityOnSalesOrder, QuantityOnPurchaseOrder
FROM IM_ItemWarehouse
WHERE ItemCode = '1001-HON-H252'

Only what changed since the last sync

SELECT CustomerNo, CustomerName, DateUpdated, TimeUpdated
FROM AR_Customer
WHERE DateUpdated >= {d '2026-09-20'}

Customer, sales order and item tables carry DateUpdated and TimeUpdated columns, so an incremental sync does not have to re-read every row.

5. Where to find table and field names

Sage publishes every table in its "File Layouts and Program Information" help. The main ones: AR_Customer, SO_SalesOrderHeader, SO_SalesOrderDetail, CI_Item, IM_ItemWarehouse, AR_OpenInvoice, AR_InvoiceHistoryHeader, AP_Vendor, PO_PurchaseOrderHeader and GL_DetailPosting.

Sources: Sage 100 file layouts, Sage community: DSN-less connection string, ProvideX SQL syntax notes, Stack Overflow: Python and the MAS 90 driver.