Uniface User Forum

 View Only
  • 1.  Datetime field overflow

    Posted 12-16-2021 04:15
    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.
    ------------------------------


  • 2.  RE: Datetime field overflow

    ROCKETEER
    Posted 12-16-2021 05:08
    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
    ------------------------------



  • 3.  RE: Datetime field overflow

    ROCKETEER
    Posted 12-16-2021 05:53
    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
    ------------------------------



  • 4.  RE: Datetime field overflow

    Posted 12-16-2021 08:02
    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.
    ------------------------------



  • 5.  RE: Datetime field overflow

    ROCKETEER
    Posted 12-16-2021 08:16
    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
    ------------------------------