BusinessObjects Board

Problem with dates (fractional truncation/datetime field overflow)

dataservices
#1

Hello all,
I have a strange problems with dates.
I’m running an ETL process on Data Services 4.2 SP7.
Source and target DB are both SQL Server 2017.

When there is a date like 01-01-01 (default dates used in the source DB) or a date in the year 2100 the ETL throws this error:

  • “ODBC data source error message for operation : <[Microsoft][ODBC SQL Server Driver]Fractional truncation [Microsoft][ODBC SQL Server Driver]Datetime field overflow>.”
  • “Function call <raise_exception ( STOPPED DUE TO AN EXCEPTION IN THE CURRENT JOB ) > failed, due to error <50316>: .”

I have 3 environments (they are 3 different servers with 3 different installations): development, test and production. This error only happens in the test environment. Dev and Prod works without any issues with those dates.
The connections to the source and target databases are all made in the same way, using SQL 2014 compatibility (the highest available in this version of DS).

I’ve identified the dates as the source of the problem because if I update them to the current year everything works.

The query transform object does not apply any transformation to these dates, it just copy them to the target table. In both source and target tables the datatype is Datetime2(7), and the field in the Query Transform is treated as Datetime.

Is there anywhere an hidden configuration I must check to have these dates processed correctly as in the other two servers? I cannot find anything…

Thank you in advance.

#2

Might be worth taking a look at the Tools-Options-Data-General. Compare the century year setting across your servers.

#3

Hello, thanks for your answer.
I’ve already checked this, but they were already configured in the same way.

I’ve also checked the international settings in the Windows settings: they were different, but even if I set them all the same the Data Services job still throws the same error…

#4

Which version of Windows?
Reaching for straws here, but under Control Panel check the Date and Time settings including the timezone.

You said you checked the “international settings” - did you mean the Region tool in the Control Panel?

#5

Hello,
in the Dev environment there is a Windows Server 2012 R2.
In Production and Test environments there are Windows Servers 2019 Standard.
Yes, I’ve checked both Date&Time and Region settings.
There were some differencies, but changing them gave no effect.

The SQL servers are all installed in English, and they all have the same Collation.

#6

Hello again.
After some time I finally found the solution.
On the test server there was missing the SQL Server Native Client 11, so the Data Service job was using some other generic driver not fully compatible with data types.
On the production server the problem is not occurring because it has a local database for repositories, and the native client was already installed with sql engine.