Issue with Capability Request Preview using .NET SDK after Upgrading to FNO OnPremise 2023 R1

[#1]

Summary

After installing the 2023 R1 and upgrading the FNO, CLS, and UAS databases from an older FNO version, attempting to preview available features for a device from an FNE.NET KIT results in a "Back Office Server Error". The error code is [1,7E6,9,0[7000001C,7100002E,250137]].

Symptoms

When trying to use the FNE .NET Toolkit for Capability Request Previews the error below screenshot is thrown. The error describes an issue with an un-supported datatype on the table gls_feature. The DB used here is AWS RDS SQL Server Standard Edition.

Steps to Reproduce

1. Prepare the Environment:

• Import a customer database that has not been upgraded to the latest version.

2. Upgrade CLS Database:

• Access the FNO setup page: "http://localhost:4321/flexnetsetup"
• Use the "Manage Schema" option for the CLS database.

3. Deploy and Start Services:

• Deploy the FNO, UAS, and LFS services.
• Start the FNO service, ensuring it points to the recently upgraded customer database (not the original, non-upgraded one).

4. Trigger the Error:

• Use the .NET FNE KIT to send a "Preview Capability Request".

 Expected Result:

• The .NET FNE KIT should show the available features

Results we are getting:

• The .NET FNE KIT is throwing an error.

Workaround

The FNO upgrade scripts fail to upgrade the data types of a few columns on the table "gls_feature". TEXT datatype of a column is deprecated in the SQL server and some of the versions of SQL server don't support having distinct TEXT fields. Changing the columns with TEXT datatype to Varchar(Max) is recommended.  The below steps are performed on the DB.

1. Check if ALLOW_SNAPSHOT_ISOLATION is on for clsdb or not

   The below query can be used to check the same : 

   SELECT snapshot_isolation_state_desc from sys.databases where name='clsdb';

   If not enabled use the below query to enable the same :

   ALTER DATABASE clsdb SET ALLOW_SNAPSHOT_ISOLATION ON;

   In this case, the ALLOW_SNAPSHOT_ISOLATION was off for this DB and we had to enable to make changes to the table structure of dbo.gls_feature

2. Change datatypes for a few columns on table dbo.gls_feature

   Kindly execute the below queries :

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN vendor varchar(MAX);

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN issuer varchar(MAX);

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN issued datetime;

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN notice varchar(MAX);

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN serialnumber varchar(MAX);

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN starts datetime;

   ALTER TABLE clsdb.dbo.gls_feature
   ALTER COLUMN expiry datetime;

Fix Version and Resolution

The BUG will be fixed in future releases.