Skip to main content
Skip table of contents

Error at the updating database phase during the Imagicle UCX suite upgrade or during the restore phase

Description

If coming from a very old Imagicle suite installation, even if kept up to date, it may occur that upgrading to the 2021.6.1 release, the operation fails at the database updating phase.
The same could happen during the restoration of a backup take from an old version

Logs contained in BillyBlues.Core.Wizard folder shows the following error: 

CODE
Exception Type {System.Data.SqlClient.SqlException}
Message {Column 'dbo.Siti.sNome' is not the same data type as referencing column 'Users.user_SiteName' in foreign key 'FK_Users_Siti'.
Could not create constraint or index. See previous errors.}
StackTrace {
  in Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
  in Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)}
}

Cause

The “sNome” column type of table “Siti” is VARCHAR(50) instead of NVARCHAR(50)

image-20250114-085133.png

With 2021.Summer.1 has been introduced into DB schema an FK that references the “sNome” Column on “Siti” table that could trigger the issue.

Solution

If it happens during the upgrade follow this procedure on the database where the upgrade is failing
If it happens during a restore, follow this procedure on the database of the old server and then take a new backup using our backup tool

Manually change the type of the column “sNome” to NVARCHAR(50) by executing the below query on the database with the SQL Management Studio (to be done on both nodes in case of cluster):

CODE
ALTER TABLE Siti ALTER COLUMN sNome NVARCHAR(50) NULL

If an error occurs

image-20250114-085154.png

Follow this guide

  1. remove the FK_Users_Siti foreign key from Users table
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_Users_Siti]

  2. remove the UQ_Siti_sName index from Siti table
    ALTER TABLE [dbo].[Siti] DROP CONSTRAINT [UQ_Siti_sNome]

  3. change the type of the column sNome
    ALTER TABLE Siti ALTER COLUMN sNome NVARCHAR(50) NULL

  4. add again the UQ_Siti_sName index to Siti table
    ALTER TABLE Siti ADD CONSTRAINT UQ_Siti_sNome UNIQUE (sNome)

  5. change the type of the column user_SiteName
    ALTER TABLE [dbo].[Users] ALTER COLUMN user_SiteName NVARCHAR(50) NULL

  6. add again the FK_Users_Siti foreign key to Userstable
    ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Siti] FOREIGN KEY([user_SiteName])REFERENCES [dbo].[Siti] ([sNome])ON UPDATE CASCADE

  7. re-run the UCX Suite installer

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.