- Flexera Community
- :
- FlexNet Manager
- :
- FlexNet Manager Forum
- :
- Data warehouse export task failing after upgrade to 2020 R1.2
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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... 🙂
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
This thread has been automatically locked due to inactivity.
To continue the discussion, please start a new thread.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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">
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Team,
I have made suggested change but still getting same error.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am working on on-premise setup.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please let me know once you get the fix & issue get resolved.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks.
Can you share xml file. So that I can also test.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@ChrisG @Kevin_Hou: i tried the 'DR' XML and it worked. Now Datawarehouse task processed successfully.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using the file you provided fixed the issue. Strange it did not update as part of the upgrade.
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
