3305 Main Street Ste 019 Vancouver, WA 98663

Queries with clauses in non-standard sequence fail after upgrading from Sage 100 Contractor v19 to v20

Background

The upgrade from Sage 100 Contractor v19 to v20 represents a major change to the underlying database. Beginning with v20, Sage 100 Contractor now uses a Microsoft SQL Server database. Because of this, the database driver (which provides the connectivity between Liberty Reports and your Sage 100 Contractor data) is different and has different capabilities and limitations.

ANSI Standard SQL specifies that the parts (clauses) that make up a SQL statement must occur in a specific sequence -

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

The legacy database driver did not enforce this sequence. It was therefore possible to hand author a SQL statement (i.e., not built from the Liberty Reports query wizard) that placed some of these clauses in the wrong sequence (e.g., WHERE after ORDER BY). The Microsoft SQL Server ODBC driver enforces sequence.

Symptom
  • Symptom 1 - A popup message is displayed indicating that one of your queries could not be refreshed. If you right-click the range of cells containing the query and click Refresh Data, the following message is displayed:
    [Microsoft][ODBC SQL Server][SQL Server]Incorrect syntax near the keyword 'clause'.
  • Symptom 2 - A Liberty Reports function (such as DBLookup or DBFind) does not return any value and the following message is written to the log:
    [Microsoft][ODBC SQL Server][SQL Server]Incorrect syntax near the keyword 'clause'..
Solution

Edit the SQL text of the query (or the SQL text being passed to the Liberty Reports function) and arrange the parts of the SQL text in the correct sequence (as noted above) -

Before Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name FROM   actpay ORDER BY   vndnme WHERE   state_='CA'

After Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name FROM   actpay WHERE   state_='CA' ORDER BY   vndnme


See Also -
Common issues with custom reports after upgrading from Sage 100 Contractor v19 to v20