Post FNMS upgrade getting Invalid object name 'dbo.importedtechnopediaarlmappedproduct' while reconciliation

[SenthilNathan]

We have upgraded the FNMS from 2019 R2 to 2020 R2 and the upgrade was successful.  Post upgrade, we started to encounter an error while the reconciliation.

We are getting "Failed to execute Writer 'Populate temporary table from imported Technopedia releases' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Technopedia.xml, at step line 1
Error: Invalid object name 'dbo.importedtechnopediaarlmappedproduct'."

checked the DB and queried "SELECT * FROM ImportedTechnopediaARLMappedProduct" I can see the table exists and column headers are visible, however, no value or data returned.

Checked the Flexera community but I can't find any similar issues in the past.

Did anyone encounter this issue before?

[ChrisG]

One other thing to verify is that the ImportedTechnopediaARLMappedProduct view can be accessed using the all lower-case name noted in the error message:

SELECT * FROM dbo.importedtechnopediaarlmappedproduct

It would be unusual for this not to work if the query you have already tried does work, but maybe you have is some strange case sensitive schema setting set up.

[SenthilNathan]

@ChrisG : I will request the client to run the query using the all lower-case

select * from dbo.importedtechnopediaarlmappedproduct

However, I have the following question

1. If I want to check the upgrade package (Database Migration for FlexNet Manager Suite 2020 R2\Normal) contains the "dbo.importedtechnopediaarlmappedproduct" and also the upgrade log to confirmed whether the "dbo.importedtechnopediaarlmappedproduct" was successfully installed/updated, is it possible?

[SenthilNathan]

@ChrisG : I tried the following and found that the lower case query is failing

arl_mapped_product_lower_case

arl_mapped_product_lower_case

ARL_MAPPED_PRODUCT_UPPER_CASE

ARL_MAPPED_PRODUCT_UPPER_CASE

For testing, I tried on my test machine with the same FNMS version, even the lower case is successfully showing the table and the fields.

Attached the "Technopedia.xml" for reference

[Big_Kev]

Hi @SenthilNathan 

When did you get on customer system if you run below statement on FNMSCompliance

SELECT * FROM sys.objects where name like '%ImportedTechnopediaARLMappedProduct%'

kev

[bheadley]

One thing to check on the database and the database server is if they are set to Case Insensitive or Case Sensitive. Here is a page showing how to do that: https://www.webucator.com/article/how-to-check-case-sensitivity-in-sql-server/. I would recommend that it be set to Case Insensitive as the SQL in the step that is failing seems to use both "importedtechnopediaarlmappedproduct" and "ImportedTechnopediaARLMappedProduct" for some reason.

Here is a quick description of how to change Case Sensitivity: https://www.sqlservercentral.com/forums/topic/how-to-change-the-database-server-for-case-sensitive#:~:text=To%20set%20or%20change%20the%20database%20collation&text=If%20you%20are%20creating%20a,the%20Collation%20drop%2Ddown%20list.

If you are using an on-prem instance, the other thing you could do is edit the writer file "Technopedia.xml" and change the table referenced in the "Populate temporary table from imported Technopedia releases" step to use "ImportedTechnopediaARLMappedProduct" at around line 102 though there may be other places this happens in other writer files as well that I cannot think of off the top of my head.

- Bill

[SenthilNathan]

@bheadley: thanks and in fact I was also reading this and some suggested to set values like 0 or 1 or 2 for lower case as per the link below, however I did not attempted these.

Mysql table name not working in uppercase - Stack Overflow

What exactly need to be changed?

[bheadley]

Hi @SenthilNathan ! Sorry if there was any confusion caused by my response. It's actually a little before what your screenshot shows where the change needs to be made:

Change 'dbo.importedtechnopediaarlmappedproduct' to 'dbo.ImportedTechnopediaARLMappedProduct' at this line in the FROM clause in step 102, 'Populate temporary table from imported Technopedia releases'. 

The link you sent regarding case sensitivity appears to be MySQL related. Are you running the databases using Microsoft SQL Server or MySQL? In the case of SQL Server another link with information on changing this can be found here:https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-ver15. If you need assistance with any of this please make sure to open a support case and point the support engineers to this conversation.

Thanks,

Bill

[SenthilNathan]

Hi @bheadley, thanks for pointing the exact line.  I will request the client to update the Technopedia.xml and try it.

Sorry, I did not notice it is for MySQL.  We are using Microsoft SQL not MySQL for the FNMS solution.

On the case sensitivity query, please refer to the following screenshot.  It looks like the collation setting matches as per the FNMS system requirement.

[SenthilNathan]

Hi @bheadley: the issue is solved by updating the "Technopedia.xml" file.

Thanks for your help.

[bheadley · Accepted solution]

@SenthilNathan ,

That's great to hear! I am working to get an escaped defect logged on the Flexera side so this will not happen again with future migrations.

Thanks again!

Bill

[bheadley]

Just to follow up on this, a bug was logged and the issue has been corrected. This should no longer exist in future releases and if there is anyone who runs into this in past releases then you can open a support case to get it corrected. Thanks!