3305 Main Street Ste 019 Vancouver, WA 98663

Queries containing TRIM function no longer work 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.

Using the legacy database, queries may have been customized in some cases (by editing the SQL statement) to use the TRIM function. This function can be useful because it removes any leading or trailing spaces from a text value. The TRIM function is not supported by the SQL Server ODBC driver however. Instead, two other functions, LTRIM and RTRIM, take its place.

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]'TRIM' is not a recognized built-in function name.
  • 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]'TRIM' is not a recognized built-in function name.
Solution

Edit the SQL text of the query (or the SQL text being passed to the Liberty Reports function) and replace TRIM(...) with LTRIM(RTRIM(...)). Example -

Before Correction

SELECT   recnum AS Vendor,   TRIM(vndnme) AS Vendor_Name,   shtnme AS Short_Name,   ownnme AS Owner FROM   actpay

After Correction

SELECT   recnum AS Vendor,   LTRIM(RTRIM(vndnme)) AS Vendor_Name,   shtnme AS Short_Name,   ownnme AS Owner FROM   actpay


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