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 optional here. Microsoft's ODBC reference requires them only when the driver name contains a semicolon (SQLDriverConnect, Comments). They do no harm, and the examples in these guides keep them.
• 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; what breaks in 2026 and how to fix it. 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.

Want typed records and pandas DataFrames instead of raw cursor rows? Guide 06: Sage 100 data in Python with hundred-odbc and pandas reads customers, open orders and items, and lists the common connection errors.

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, Microsoft: ODBC connection string syntax.