UIM 20.4CU4 upgrade fails with SQL exception at 25 percent on MSSQL 2014 or 2016 - sqlserver

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Upgrading UIM from 20.4 or 20.4CU3 fails if the backend database is hosted on MSSQL 2014 or 2016:

in the uimserver_ia_install.log the following error is seen:

Executing SQL command: CREATE OR ALTER FUNCTION [dbo].[splitstring] ( @stringToSplit VARCHAR(MAX),@del Char(1) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(@del, @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@del, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END
2022-08-31 21:42:00,347 ERROR impl.UIMServerDBSchemaController:run:173 [Thread-39] - NimException caught
(503) , com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.: Incorrect syntax near the keyword 'OR'.
at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.runDBScript(NimDBScriptRunner.java:78)
at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScript(NimDBProviderBase.java:260)
at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScriptFile(NimDBProviderBase.java:193)
at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.runScriptCommon(UIMServerDBSchemaController.java:427)
at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.configureumpinventorycolumninfo(UIMServerDBSchemaController.java:405)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:532)
at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:516)
at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure_dbScript(UIMServerDBSchemaController.java:273)
at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure(UIMServerDBSchemaController.java:184)
at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.run(UIMServerDBSchemaController.java:171)
at java.lang.Thread.run(Thread.java:750)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'OR'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:729)
at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.executeSQLCommand(NimDBScriptRunner.java:105)
at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.runDBScript(NimDBScriptRunner.java:60)
... 14 more

Environment

Release : 20.4

Component : UIM - INSTALL

Cause

- defect

Resolution

This is being investigated by development.

MSSQL 2016SP1 or MSSQL 2019 will work fine but MSSQL 2016 (without SP1) fails as does MSSQL 2014.

If you are able to upgrade MSSQL to at least 2016SP1 you will avoid this issue.

Note: Support for MSSQL 2014 and 2016 have NOT been dropped; we are treating this as an urgent defect. A refreshed version of the installer will be released.

If you run into this issue, you may cancel the installation and then you may need to log in using Infrastructure Manager and restart some/all of the probes on the primary hub. This will allow you to continue using your existing version, you do not need to roll back.

Additional Information

To download MS SQL Server 2016 SP1:

Microsoft® SQL Server® 2016 Service Pack 1 (SP1)

VSTS bug number KB Article Description

8103261 3190548 An update to introduce a new Transact-SQL statement CREATE OR ALTER in SQL Server 2016

After the SP1 update it should display output of the query SELECT @@VERSION for example as: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

Note that your data_engine may end up in a 'bad state' e.g., data_engine probe displays as green but doesn't have a port or a PID. Log may also show 'nimEnd' and logging is stopped.

If this is the case, and the installation progress seems stalled/not progressing any further, e.g., showing only 45% completed, you can try redeploying the same data_engine probe version while the process is still underway and hasn't timed out yet.

Then the UIM Server should continue processing and finish successfully.