Upgrade to ZENworks 2017 fails because of NTEXT column in the database

  • 7021050
  • 30-Jun-2017
  • 19-Aug-2021

Environment

Novell ZENworks Configuration Management 2017 System Update
Microsoft SQL Server Database

Situation

  • Upgrading the first Primary Server to ZENworks 2017
  • ZENworks zone has been upgraded from ZENworks 10.x
  • The upgrade to ZENworks 2017 fails on the database schema upgrade

The following messages are seen in the pre-global-actions.log:

ERROR:
[SystemUpdate] [] [Attempt 5 of executing SQL (update zSystemSetting set Data=REPLACE(data,'<SkipDirectories>','<SkipDirectories><Item>C:\$Recycle.Bin</Item>') where Name='inventorysw'
and CHARINDEX('C:\$Recycle.Bin',Data)=0;) failed with exception : Argument data type ntext is invalid for argument 1 of replace function.] [java.sql.SQLException: Argument data type ntext is invalid for argument 1 of replace function.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
...
[SystemUpdate] [] [Exception while executing SQLs from /system-updates/v_17_0_0/sql-server/assetmanagement_update.sql] [] [] [] [PreGlobalAction]
[SystemUpdate] [] [Exception while executing action /system-updates/v_17_0_0/sql-server/assetmanagement_update.sql for version : v_17_0_0] [com.novell.zenworks.systemupdate.exceptions.PreGlobalException: com.novell.zenworks.systemupdate.exceptions.PreGlobalException: Error executing SQL: update zSystemSetting set Data=REPLACE(data,'<SkipDirectories>','<SkipDirectories><Item>C:\$Recycle.Bin</Item>') where Name='inventorysw'
and CHARINDEX('C:\$Recycle.Bin',Data)=0;
        at com.novell.zenworks.systemupdate.utils.SQLUtils.executeSQLStatements(SQLUtils.java:245)
...
Caused by: com.novell.zenworks.systemupdate.exceptions.PreGlobalException: Error executing SQL: update zSystemSetting set Data=REPLACE(data,'<SkipDirectories>','<SkipDirectories><Item>C:\$Recycle.Bin</Item>') where Name='inventorysw'
and CHARINDEX('C:\$Recycle.Bin',Data)=0;
        at com.novell.zenworks.systemupdate.utils.SQLUtils.executeSQLStatements(SQLUtils.java:204)
...
Caused by: java.sql.SQLException: Argument data type ntext is invalid for argument 1 of replace function.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
...
[SystemUpdate] [] [Execution of update action : /system-updates/v_17_0_0/sql-server/assetmanagement_update.sql of type : EXEC_SQL failed. Check logs for more details.] [] [] [] [PreGlobalAction]
[SystemUpdate] [] [Failed to update schema to version : v_17_0_0. Check pre-global logs for more details.] [] [] [] [PreGlobalAction]
[SystemUpdate] [] [Exception during pre-global-actions] [com.novell.zenworks.systemupdate.exceptions.PreGlobalException: Schema upgrade failed. Check the pre-global-update.log for more details.
        at com.novell.zenworks.systemupdate.PreGlobalAction.processAction(PreGlobalAction.java:380)
...
[SystemUpdate] [] [Setting the status to FAILED for update : 5017000000fc50000000002016121615] [] [] [] [PreGlobalAction]

Resolution

  1. Run the following verification query on both the ZENworks and Audit databases:
    select * from DeprecatedDataTypesLog;
  2. If the query from Step 1 returns any rows, please stop this process and contact Micro Focus Customer Support, who will assist with below queries.
  3. Stop all Primary Server and Reporting Server services
  4. Take a full backup of the ZENworks and Audit databases
  5. Run this script on both the ZENworks database and the Audit database using Microsoft SQL Server Studio:
    declare @V_ERROR_NUMBER BIGINT ,@V_ERROR_MESSAGE NVARCHAR(MAX)
    exec [dbo].[replaceDeprecatedDataTypes] @V_ERROR_NUMBER OUTPUT,@V_ERROR_MESSAGE OUTPUT
    select @V_ERROR_NUMBER,@V_ERROR_MESSAGE
  6. If there are no error messages, then run this query to check If any deprecated data type columns are left:
    SELECT  O.NAME , C.NAME , T.NAME  FROM sys.columns C  JOIN sys.types T ON C.system_type_id = T.system_type_id  JOIN sys.objects O ON C.object_id = O.object_id  JOIN sys.schemas S ON O.schema_id = S.schema_id  WHERE T.NAME IN (    'text'    ,'ntext'    ,'image'    )   AND O.type = 'U';
  7. Start all Primary Server and Reporting Server services

Cause

The REPLACE function called as part of the upgrade is not valid for TEXT and NTEXT type columns. These column types are left over from an earlier version of ZENworks.

Additional Information

In recent ZENworks versions, the TEXT, NTEXT, and IMAGE columns have been changed to VARCHAR, NVARCHAR, and VARBINARY respectively. Normally, these column types would be changed in the regular upgrade process. In some cases the version that changes these can be missed.