UIM 20.4CU4 upgrade fails with SQL exception at 25 percent on MSSQL 2014 or 2016 - sqlserver_umpinventorycolumninfo.sql script
search cancel

UIM 20.4CU4 upgrade fails with SQL exception at 25 percent on MSSQL 2014 or 2016 - sqlserver_umpinventorycolumninfo.sql script


Article ID: 249144


Updated On:


DX Unified Infrastructure Management (Nimsoft / UIM)


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) )
 @returnList TABLE ([Name] [nvarchar] (500))


 WHILE CHARINDEX(@del, @stringToSplit) > 0
  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)

 INSERT INTO @returnList
 SELECT @stringToSplit

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



Release : 20.4

Component : UIM - INSTALL


- defect


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.