3305 Main Street Ste 019 Vancouver, WA 98663

Queries with double-quotes around text literals 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.

Queries with the legacy database would allow literal text values to be enclosed in either single-quotes or double-quotes. Literal text values specified when building queries using Liberty Reports wizards are always enclosed by single-quotes. The Microsoft SQL Server ODBC driver only supports the use of single-quotes to enclose literal text values. The Microsoft SQL Server ODBC driver interprets values enclosed by double quotes as references to database objects (schema names, table names, or column names).

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 Driver][SQL Server]Invalid column name 'text-value'.
  • 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 Driver][SQL Server]Invalid column name 'text-value'.
Solution

Edit the SQL text of the query (or the SQL text being passed to the Liberty Reports function) and replace double-quote text enclosers with single-quotes. Example -

Before Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name,   addrs1 AS Address_1,   addrs2 AS Address_2,   ctynme AS City,   state_ AS State,   zipcde AS Zip FROM   actpay WHERE   state_="CA"

After Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name,   addrs1 AS Address_1,   addrs2 AS Address_2,   ctynme AS City,   state_ AS State,   zipcde AS Zip FROM   actpay WHERE   state_='CA'


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