Known Issue: Data warehouse export step "Snapshot Optimization_Snapshot_FACT data" may fail with error: "Conversion failed when converting date and/or time from character string." (IOK-1005577)

[#1]

Summary

The “Snapshot Optimization_Snapshot_FACT data” step of data warehouse export processes may fail with output in the export log file similar to:

2022-12-30 14:40:14,078 [INFO ]       Snapshot Optimization_Snapshot_FACT data
2022-12-30 14:40:14,078 [INFO ]         Failed to execute Exporter 'Snapshot Optimization_Snapshot_FACT data' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Exporter\fnmpdatawarehouse\MetricDataExport.xml, at step line 6
Error: Conversion failed when converting date and/or time from character string.
LatestSnapshotDate : 30/12/2022 0:00:00

Cause

This issue is caused by a logic error handling date values with various regional settings that use different date formats.

Mitigation

Hotfix 2 for FlexNet Manager Suite 2022 R2 has been published containing a fix for this issue.

An earlier mitigation published for this issue involved setting an internal configuration setting “KPIDataExport” to the value “0”. If this mitigation was applied, the setting should be reverted to its default value of “1” after applying Hotfix 2 by executing the following SQL statement against the data warehouse database:

UPDATE cts
SET SettingValue = '1'
FROM dbo.ComplianceTenantSetting cts
    JOIN dbo.SettingName sn ON sn.SettingNameID = cts.SettingNameID
WHERE sn.Name = 'KPIDataExport'

Affected versions

This issue affects FlexNet Manager Suite 2022 R2.

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2022 R2.1 / Jan 2023 (Cloud), 2023 R1 (On Premises)

Other information

Affected components: Inventory import (read/write/export), Software license management

Master issue ID: IOK-1005577

Also known as: FNML-77334

[stefange]

Hello,

After the upgrade to 2022 R2 (on-prem) I had above issue. Setting the KPIDataExport setting to '0' did not fix the issue and the step was still being executed. In the reader in step "Snapshot Optimization_Snapshot_FACT data" the following filter runs: 

IF (@ExportKPIData = '0')

     BEGIN

So changing this setting to 0 will not work and the reader will still executed the step.

I changed the setting to 1 and now the step is being skipped

UPDATE cts
SET SettingValue = '1'
FROM dbo.ComplianceTenantSetting cts
    JOIN dbo.SettingName sn ON sn.SettingNameID = cts.SettingNameID
WHERE sn.Name = 'KPIDataExport'

[ChrisG]

@stefange - the default value for the "KPIDataExport" setting is "1", so if the export process is working for you with that setting value configured then you are not being affected by this issue. You do not need to change the setting in that case.

In relation to your comment about the "Snapshot Optimization_Snapshot_FACT data" step being executed: this export step is always executed, regardless of the value of the setting. However there are SQL statements in the step that are executed when the setting is "0", and there are different statements in the step (after the "IF" statement you have noted above) that are executed when the setting is "1".

The particular issue discussed in this article is evident (on some systems) when the setting is "1".

[stefange]

Hi Chris,

I did some more testing. The step "Snapshot Optimization_Snapshot_FACT data" fails with both settings ('1' and '0'). 

The Mitigation does not work for me

Stefan 

[msutharh]

Same Mitigation is not working for me.

[stefange]

Hi,

I removed the Exporter.config step for the MetricDataExport.xml and now it works fine. Is there another fix for this?

Stefan

[ChrisG]

I understand a hotfix for this issue is being considered (in addition to the updates planned as noted in the "Fix status" section of this article).

But in the meantime, if the process is failing regardless of the value of the "KPIDataExport" setting then you may need to reach out to Flexera Support for specific assistance. It's possible you could be getting affected by a slightly different issue, or it may be that it is the same issue as what is described here but the documented mitigation is only effective in some circumstances.

[ChrisG]

One other thing to check is that you are configure the KPIDataExport setting in the data warehouse database. You're not trying to configure it in the compliance database are you?

[msutharh]

yes, we have executed only in the data warehouse database.

[steve_young]

ANOTHER UPDATE...
Forget the last update, it is still failing with it set to 0, so I have set it back again to 1.
Will have to open a support call after all and see if the support guys can sort it out.

UPDATE....
It was a problem with the script as we are using a Multi-Tenant DB, so I just did it the hard way and checked the table to get the ID number that references the KPIDataExport value from the settings table and then just set the value to 0 against all entries with the ID in the ComplianceSetting table.
(5 rows affected) this time and looks like it is skipping the issue for the time being.

ORIGINAL POST....
I have the same issue, problem for me is that when I run this script against the FNMSDatawarehouse DB I am getting (0 rows affected) so it is not actually changing anything.

We are running a Multi-tenant version of FNMS 2022 R2.

Guess I better open a support call and see if the support guys can help

 

 

[MurrayPeters]

Hi all,

A workaround that works for me is to insert the following line as the first instruction in the Step "Snapshot Optimization_Snapshot_FACT data" (order = 155).

SELECT TOP 1 @LatestSnapshotDate=SnapshotDate from Snapshot_DIM order by SnapshotDate DESC

The variable "@LatestSnapshotDate" is intended to be set via the previous step called "GetLatest Snapshot Date" (Order=150), but the variable is being reflected back in the subsequent step as a full datetime string instead of a yyyy-mm-dd (hence the SQL error that is being seen).

Here is what the file extract looks like (highlighted in yellow is the line added):  

Of course, please defer to Flexera formal advice on the issue, but just thought I'd share this as a workaround.

regards,
Murray

[steve_young]

Perfect, worked for me and the Datawarehouse export has completed on all our tenants, so that was a win. Now to look into the ARL issue I have and raise another call with the support guys.

[mfranz]

This sounds like a bug I filed in Jan 2021 with a failed implicit type conversation in the data warehouse export.

[ChrisG]

Hotfix 2 for FlexNet Manager Suite 2022 R2 has now been published containing a fix for this issue.

CC @GodfreyTam, @steve_young, @stefange, @MurrayPeters, @msutharh