Environment
Novell ZENworks Configuration Management 2017 System Update
Microsoft SQL Server Database
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
- Run the following verification query on both the ZENworks and
Audit databases:
select * from DeprecatedDataTypesLog; - 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.
- Stop all Primary Server and Reporting Server services
- Take a full backup of the ZENworks and Audit databases
- 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 - 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'; - 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.