PopulateCustomViewsForAllTenants fails during migration from FNMP 9.2 with duplicate "Licenses" fold...

‎Dec 19, 2018

Summary

This article describes a known issue that may occur during migration from FlexNet Manager Platform 9.2 to FlexNet Manager Suite 2018 R2.

Symptoms

The PopulateCustomViewsForAllTenants stored procedure in the cm-migr1000-ComplianceSchema.sql script published with the FNMS 2018 R2 release is failing to handle the state of data in the FNMP 9.2 database.

The PopulateCustomViewsForAllTenants stored procedure calls the RegisterDefaultSystemCustomViewFolders, which in turn fails with the error:

Preparing to run step 'Migration from 9.3.6 to 10.0'.
	Required minimum database compatibility level: SQL Server 2008 (100)
	Found database compatibility level: SQL Server 2008 (100)
	Required version: 9.3.6
	Found version: 9.3.6
	Executing step
	Checking that all database objects are owned by dbo...Validated
	Running 'cm-migr1000-migprep.sql'.
	Running 'cm-migr1000-ManageSoftSchema.sql'.
	Running 'cm-migr1000-ManageSoftProcedures.sql'.
	Running 'cm-migr1000-ComplianceSchema.sql'.

		There was an error while attempting to run 'cm-migr1000-ComplianceSchema.sql'.
		Violation of UNIQUE KEY constraint 'UQ_ComplianceSearchFolder_Key'. Cannot insert duplicate key in object 'dbo.ComplianceSearchFolder_MT'. The duplicate key value is (Licenses, -2, 2, 1).
		The statement has been terminated.

		Stack Trace

		   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
		   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
		   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
		   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
		   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
		   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
		   at ManageSoft.Database.Update.FileStep.ExecuteNonBCP(SqlConnection p_SqlConnection)

		SQL Statement

		
		EXEC PopulateCustomViewsForAllTenants
		

	File steps for Migration from 9.3.6 to 10.0 failed.

Cause

The 1st screenshot below shows the details in the ComplianceSearchFolder table before commencing the migration.

The 2nd screenshot shows the state of the ComplianceSearchFolder table after the failure has occurred.

The particular query that is causing the error is the last query in the RegisterDefaultSystemCustomViewFolders stored procedure:

UPDATE csf 
SET 
       ParentFolderID = cvfh.ParentFolderID, 
       Path = cvfh.Path 
FROM dbo.ComplianceSearchFolder_MT AS csf 
JOIN #CustomViewFolderHierarchy AS cvfh 
       ON cvfh.ComplianceSearchFolderID = csf.ComplianceSearchFolderID 
WHERE 
       csf.TenantID = @TenantID

the migration procedure is not cleanly handling the existing ?Custom Views/Licenses Views/Licenses? custom view folder in the 9.2. system: when it tries to add a system ?Reports/License Compliance/Licenses? folder in the migrated system, it is failing because the primary key conflicts with the existing folder.

Workaround

A workaround is to rename existing ?Licenses? folder to ?Licenses (old folder)? prior to running the migration:

update ComplianceSearchFolder set Name = 'Licenses (old folder)' where ComplianceSearchFolderID = 1003

mfranz · ‎Jan 28, 2020

I just had a similar error migrating from 13.2 to 13.3. Root cause seemed multiple ComplianceSearchFolderSystemIDs for manually created folders. Custom folders should have NULL for this field. As soon as the non-standard ComplianceSearchFolderSystemIDs were removed, (NULL), the process succeeded.