3305 Main Street Ste 019 Vancouver, WA 98663

Queries having parameters in the SELECT list 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.

The legacy database supported the use of parameter markers (?) in the SELECT list. The Microsoft SQL Server ODBC driver does not.

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 Driver]Syntax error or access violation., followed by [Microsoft][ODBC SQL Driver]Invalid descriptor index.
  • 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 Driver]Syntax error or access violation., followed by [Microsoft][ODBC SQL Driver]Invalid descriptor index..
Solution

Remove the parameters from the SELECT list and perform any special functions using using Excel functions.

Before Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name,   LEFT(vndnme,?) AS Group FROM   actpay

Excel:

ABC
1Group Param:1
2
3VendorVendor NameGroup
413Ross Electric, IncR

After Correction

SELECT   recnum AS Vendor,   vndnme AS Vendor_Name FROM   actpay  

Excel:

ABC
1Group Param:1
2
3VendorVendor NameGroup
413Ross Electric, Inc=LEFT(B4,$C$1)

Notice that in the original version, the parameter is bound to cell $C$1. The red-highlighted text has been removed in the corrected version and the third column is now an Excel formula which you see highlighted in yellow.


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