Data warehouse export task failing after upgrade to 2020 R1.2

[sdbash]

Hi all,

My team has recently upgraded our multi-tenant platform to FNMS 2020 R1.2, and am now coming across a failing Data warehouse export task error for all of the tenants.  The error in each tenants' "importer-xyz.log" file is the same; it fails to execute an Exporter from an XML file because the conversion of a nvarchar data type to a datetime data type results in an out-of-range value.

2020-12-21 10:40:51,836 [INFO ] Delete Obsolete Snapshot_DIM data
2020-12-21 10:40:52,039 [INFO ] Failed to execute Exporter 'Delete Obsolete Snapshot_DIM data' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\\Exporter\fnmpdatawarehouse\AnalyticsDataModel.xml, at step line 9
Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
2020-12-21 10:40:52,039 [INFO ] All retries have been attempted for Exporter 'Delete Obsolete Snapshot_DIM data'
2020-12-21 10:40:52,039 [INFO ] Completed in 0 seconds
2020-12-21 10:40:52,054 [INFO ] Released application lock Exporter_550e6a18-201e-4b9f-ba3a-9f5e603ce7be_AnalyticsDataModel_44MM5SQBJZ5NUNVF
2020-12-21 10:40:52,054 [ERROR] System.Data.SqlClient.SqlException (0x80131904): The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
...
Error Number:242,State:3,Class:16

Anybody else run into anything similar?  Would really like to get this sorted before holiday break...   🙂

[ChrisG · Accepted solution]

This problem is currently actively being investigated by Flexera Support, so I would suggest you raise a case with them so your situation can be included in the investigation and hopefully a resolution. Refer your Support contact to issue "FNMS-70046".

In the meantime, I have seen a comment that somebody was able to avoid this issue on systems that use a British/French format like "dd/mm/yyyy" by adding the following SQL statement in the C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Exporter\FNMPDataWarehouse\AnalyticsDataModel.xml file at the beginning of the step "Delete Obsolete Snapshot_DIM data":

SET @SnapshotDate = CONVERT(datetime, @SnapshotDate, 103)

(Take a backup of the unmodified .xml file before making any changes so you can easily revert!)

If you have an opportunity to try this and it works to avoid the failure then that would be very useful to know.

[sdbash]

Thanks for the quick replies, @ChrisG and @Kevin_Hou 🙂

I used Chris' suggestion and added the SQL line to the XML file and it looks to have sorted things out.  FNMPDataWarehouse task is now completing without error across all the tenants.

Cheers!

[durgeshsingh]

Please confirm if you have added line like below 

</Exporter>

<!--Delete snapshots that no longer fit the monthly or weekly duration criteria from the data warehouse database.-->
<Exporter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:type="ExecuteOnDestination"
SET @SnapshotDate = CONVERT(datetime, @SnapshotDate, 103)
Name="Delete Obsolete Snapshot_DIM data"
Order="200"
Retries="1">

[durgeshsingh]

Team,

I have made suggested change but still getting same error.

[sdbash]

I ended up raising a proper support case to reach a resolution. As Chris had mentioned, it was confirmed that this was related to a known issue with FNMS R1.2, in which the fix was only applied to the FNMS Cloud environment. The engineering team was able to provide a hotfix.

[durgeshsingh]

I am working on on-premise setup.

[sdbash]

@durgeshsingh same here; the fix was applied only to FNMS Cloud -- I raised a case with the Flexera support team who was able to obtain the fix from the engineering team so that I could apply it to my on-prem environment.

[durgeshsingh]

Thanks buddy.

Please let me know once you get the fix & issue get resolved.

[sdbash]

@durgeshsingh my particular issue has been resolved with the assistance of the engineering team who provided some updated FileEvidence.xml writer files.

[durgeshsingh]

Thanks.

Can you share xml file. So that I can also test.

[durgeshsingh]

Team,

Please find latest status on my case. issue has got resolved after getting amended AnalyticsDataModel.xml.

This is an identified bug with the 2020R2 version of the product, IOJ-2169655, rectified in the next release.

This is due to an implicit date-type conversion and a region format mismatch between the SQL database user and the user running ComplianceReader.exe.

[SenthilNathan]

@ChrisG: Post upgrading the FNMS from 2020 R1 to R2, I started to get the error while the DataWarehouse is processing.

"System.Data.SqlClient.SqlException (0x80131904): The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

Then I tried the workaround as per the Flexera support to include the "SET @SnapshotDate = CONVERT(datetime, @SnapshotDate, 103)", after that I am getting a different error "The import process has failed with the following error (use -verbose to see error details):
Error: There is an error in XML document (106, 7). in file C:\ProgramData\FlexeraSoftware\Compliance\ImportProcedures\Inventory\Exporter\fnmpdatawarehouse\AnalyticsDataModel.xml
'@' is an unexpected token. The expected token is '='. Line 106, position 7."

Please refer to the attached XML and log file. Thanks

[SenthilNathan]

@ChrisG @Kevin_Hou: i tried the 'DR' XML and it worked.  Now Datawarehouse task processed successfully.

[Kevin_Hou]

Hi @sdbash 

We are currently verifying a DR version which is attached here. It has passed a few internal test machines.

If you would like to have a try then please backup the original XML by default located under C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Exporter\FNMPDataWarehouse\  then try the attached version.

If you are still seeing same failure with the DR, as Chris mentioned please no hesitate and contact our Flexera support team who will further help you via support process.

Cheers

Kevin  

[GopalRaghvani]

Got DataWareHouse Export failing after upgrading from 2017R2 to 2020R2.
Using the file you provided fixed the issue. Strange it did not update as part of the upgrade.

[SenthilNathan]

@GopalRaghvani: have you tried the "AnalyticsDataModel_DR.zip" and run the DataWarehouse import?

 

[GopalRaghvani]

Yes the new file worked.

[ChrisG]

For reference, the following article describes the issue discussed in this thread: Known Issue: Data Warehouse export fails with error when locale date format differs between the database user & Windows account used for running the export: "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value" (IOJ-2169655)