Upgrade to 11 SP4 (11.4.0) fails with "Error executing SQL: CREATE TRIGGER tg_zQuickTaskAsign_Ins"

  • Document ID:7017009
  • Creation Date:19-Nov-2015
  • Modified Date:19-Nov-2015
    • Micro Focus Products:
      ZENworks Configuration Management

Environment

Novell ZENworks Configuration Management 11.4 System Update
Microsoft SQL Server Database

Situation

  • Upgrading the first Primary server to ZENworks 11 SP4 (11.4.0)
  • Using Microsoft SQL Server for the ZENworks database
  • Compatibility level on the ZENworks database was set to lower than 100

The following errors could be seen in the ZENworks_Upgrade_<date>_<time>.log.xml:

ERROR:
SEVERE: Exception while upgrading database schema.
com.novell.zenworks.systemupdate.exceptions.SystemUpdateException: Unable to update database schema
    at com.novell.zenworks.systemupdate.v_11_4_0.PreGlobalAction.execute(PreGlobalAction.java:229)
...
Caused by: com.novell.zenworks.datamodel.exceptions.InternalDataModelException: com.novell.zenworks.datamodel.exceptions.InternalDataModelException: Error executing SQL: CREATE TRIGGER tg_zQuickTaskAsign_Ins ON zQuickTaskAssignment
...
Caused by: java.sql.SQLException: Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
...
@FAILED@com.novell.zenworks.systemupdate.exceptions.SystemUpdateException: Unable to update database schema
...
$UPGRADE_RESULTS$:FAILED
...
$INSTALL_SUCCESS$:NONFATAL_ERROR

Resolution

  1. Verify the current compatibility level for the ZENworks database with the following query:
    SELECT compatibility_level FROM sys.databases WHERE name = '<zenworks-database-name>';
  2. Ensure that the version of Microsoft SQL Server is a supported version (see Additional Information section below)
  3. Set the compatibility level to what it should be for the particular version of Microsoft SQL Server
    NOTE: The compatibility level must be at least 100

Cause

The compatibility level on the ZENworks database had been manually decreased at some point.

Additional Information

Please see the ZENworks 11 SP4 Database Requirements for what versions of Microsoft SQL Server are compatible with ZENworks 11 SP4.