Datetime field overflow

Hi,
Since we upgraded database to SQL Server 2016 with ODBC driver Native Client 11.0 we get errors by updating a datetime field:

Server: 22008 [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

It is for all fields which are defined as smalldatetime in the database.
We must use native client driver for Sql Server 2016 otherwise can't get connect to database.

Do you had a similar problem or is there a solution for this problem?

Best regards,
Erhan




------------------------------
Erhan Gunbulut
Sogeti Nederland B.V.
------------------------------

Hi Erhan,

Which version of Uniface are you using? And from which version of the SQL Server and client did you upgrade.

There was a problem with the SQL Server Native Client and smalldatetime in the past, and we used to have a note in the documentation:

"Environment Considerations for Microsoft SQL Server

SQL Server Native Client 10.0
The following are known issues when using SQL Server Native Client 10.0 for SQL Server 2008:

The E9 packing code (which maps to the smalldatetime storage format) fails when storing a value other than null. As workaround, consider using the SQL Server 2005 Native Client, which was delivered with SQL Server 2005, to connect to the SQL Server 2008."

Here's a old blog that describes the mentioned issue:

> SQL Server Native Client 10.0 - Datetime field overflow 

The problem also should not occur with the "old" SQL Server ODBC driver, but it does not support all the functionality provided by the Native Clients.

I hope this helps.

Thanks,
Daniel

------------------------------
Daniel Iseli
Principal Technical Support Engineer
Uniface Services
Rocket Software, Switzerland
------------------------------

Hi,
Since we upgraded database to SQL Server 2016 with ODBC driver Native Client 11.0 we get errors by updating a datetime field:

Server: 22008 [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

It is for all fields which are defined as smalldatetime in the database.
We must use native client driver for Sql Server 2016 otherwise can't get connect to database.

Do you had a similar problem or is there a solution for this problem?

Best regards,
Erhan




------------------------------
Erhan Gunbulut
Sogeti Nederland B.V.
------------------------------

Hello Erhan

The current most up to date driver is Microsoft ODBC Driver 17 for SQL Server
Information from Microsoft:
https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-2017
This driver can be used for SQL Server 2016.

The Uniface packing code for smalldate time is E9.
This when using mapping:1 or mapping:2.
However with mapping:23 Uniface expects Datetime.

When you do create table script rom Uniface do you get the needed smalldatetime ?

------------------------------
Peter Beugel
Rocket Internal - All Brands
Amsterdam NL
------------------------------

Hello Erhan

The current most up to date driver is Microsoft ODBC Driver 17 for SQL Server
Information from Microsoft:
https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-2017
This driver can be used for SQL Server 2016.

The Uniface packing code for smalldate time is E9.
This when using mapping:1 or mapping:2.
However with mapping:23 Uniface expects Datetime.

When you do create table script rom Uniface do you get the needed smalldatetime ?

------------------------------
Peter Beugel
Rocket Internal - All Brands
Amsterdam NL
------------------------------

HI Peter,
I have installed Microsoft ODBC Driver 17 for SQL Server.
But the problem still exists.

The table is not created from Uniface, we have sql scripts for that. And it's not only one table but all tables where smalldatetime is used.
About the packing code, where can I find that and is it possible to change it?

Groeten,
Erhan

------------------------------
Erhan Gunbulut
Sogeti Nederland B.V.
------------------------------

HI Peter,
I have installed Microsoft ODBC Driver 17 for SQL Server.
But the problem still exists.

The table is not created from Uniface, we have sql scripts for that. And it's not only one table but all tables where smalldatetime is used.
About the packing code, where can I find that and is it possible to change it?

Groeten,
Erhan

------------------------------
Erhan Gunbulut
Sogeti Nederland B.V.
------------------------------

Hello Erhan

The field interfaces are in the field definitions for the entity definition in the Uniface repository.
So the UCFIELD entity in the Uniface DICT model.
They can be changed but it should be done by someone whom knows the application and where it is used:
Templates might be involved and a recompile , recreating procedures  are also steps that would be needed.
 If you need help with this better open a support case via the portal.

------------------------------
Peter Beugel
Rocket Internal - All Brands
Amsterdam NL
------------------------------