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... 🙂
‎Dec 20, 2020 04:11 PM
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.
‎Dec 20, 2020 05:18 PM
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.
‎Dec 20, 2020 05:18 PM
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!
‎Dec 21, 2020 01:51 PM
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">
‎Feb 14, 2021 07:16 AM
Team,
I have made suggested change but still getting same error.
‎Feb 15, 2021 03:26 PM
‎Feb 15, 2021 03:49 PM
I am working on on-premise setup.
‎Feb 15, 2021 03:55 PM
‎Feb 15, 2021 04:03 PM
‎Feb 15, 2021 04:13 PM
‎Feb 17, 2021 02:32 PM
Thanks.
Can you share xml file. So that I can also test.
‎Feb 17, 2021 02:54 PM
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.
‎Feb 18, 2021 05:39 AM
@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
‎Apr 20, 2021 08:26 PM
@ChrisG @Kevin_Hou: i tried the 'DR' XML and it worked. Now Datawarehouse task processed successfully.
‎Apr 20, 2021 08:54 PM
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
‎Dec 20, 2020 05:54 PM
‎Aug 16, 2021 07:17 PM
‎Aug 16, 2021 08:01 PM
‎Aug 16, 2021 08:33 PM
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)
‎Dec 09, 2021 07:33 PM