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 and functions that were added to a report using the step-by-step Liberty Reports wizards will continue to function with the SQL Server database. Should you identify a case where a query or function was built using the wizard and it ceases to function correctly after the upgrade, please contact the support team.
It is possible that queries and functions may have been built by manually editing the text of the SQL statement (i.e., by not using the step-by-step wizards). Constructing SQL statements in this way allows the author of the query to utilize query capabilities that are not exposed by the Liberty Reports wizards. This includes features that may be specific to the type of database (i.e., the legacy database vs. the Microsoft SQL Server database). In these cases, it remains the responsibility of the author of the report to implement the necessary corrections to remove syntax that is only compatible with the legacy database and replace it with syntax that is compatible with the Microsoft SQL Server database.
To help facilitate the identification and resolution of upgrade-related syntax issue, the following series of articles was constructed. These articles explain each known syntax issue and how you or the author of the report can resolve it. Should you require assistance, you may engage our services team for assistance at our regular hourly rate for consulting services or project work.
Known Upgrade Issues and Resolutions
Involving custom SQL statements that were not built using the Liberty Reports wizards
Click a link for a full explanation and resolution.
- Queries containing TRIM function no longer work
- Queries with double-quotes around text literals fail
- Some SELECT DISTINCT queries fail due to incorrect ORDER BY clause
- Some GROUP BY queries fail due to incorrect ORDER BY clause
- Queries with clauses in non-standard sequence fail
- Union queries containing parameters fail
- Queries having parameters in the SELECT list fail
- Queries may not return correct results when conditioning on a nullable column
- Some PivotTables may change if the source data includes column captions with spaces