sdbash
Occasional contributor

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

Jump to solution

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...   🙂

0 Kudos
1 Solution

Accepted Solutions
ChrisG
Community Manager Community Manager
Community Manager

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

Jump to 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.

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply helped, give me a kudos. If it solves a question you have raised, please click "ACCEPT AS SOLUTION".)

View solution in original post

14 Replies
ChrisG
Community Manager Community Manager
Community Manager

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

Jump to 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.

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply helped, give me a kudos. If it solves a question you have raised, please click "ACCEPT AS SOLUTION".)

View solution in original post

Kevin_Hou
Flexera
Flexera

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

Jump to solution

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  

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".)
sdbash
Occasional contributor

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

Jump to solution

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!

durgeshsing
Active participant

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

Jump to solution

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">

0 Kudos