Problem reading from SQL/Server SmallDatetime

Author: parcaya@compuamerica.com.ve (parcaya)

When we try to read an entity SQL/Server by a field type “fecha” SMALLDATETIME returns an error - 3. We have tried different options and it does not work. Define in SQL/SErver: fecha  smalldatetime If the field is a primary key or part of it does not allow reading the entity In Uniface Model are define as (from load definition): Data Type: Combined date and time Interface: E9 Can someone help? Thanks

5 Comments

  1. Hi Pedro Use $ioprint=1023 just before the "retrieve" UnifAce will tell you the SQL-statement (in the logfile/message frame) Take this statement and excute it in an SQL-editor Maybe some variables in the WHERE are missing, this one you have to replace. On the other side, after the "read" one can use $procerrorcontext and $dberror and $dberrortext $dberror will report you the message number from the database Regards Ingo


    Author: istiller (i2stiller@gmx.de)
  2. istiller said Hi Pedro Use $ioprint=1023 just before the "retrieve" UnifAce will tell you the SQL-statement (in the logfile/message frame) Take this statement and excute it in an SQL-editor Maybe some variables in the WHERE are missing, this one you have to replace. On the other side, after the "read" one can use $procerrorcontext and $dberror and $dberrortext $dberror will report you the message number from the database Regards Ingo  

    Thank you for your quick comment. The data obtained were: Text $IOPRINT = 1023 select [ord_num] from [ord_pago] where 1=0 I/O function: S, mode: 0, on file/table: ORD_PAGO index: 2 = Where ((FECHA = 11022015 00:00:00)) Use Index is 2 is (<<A4><0><0>) FECHA is (<<A4><0><0>) select [ord_num] from [ord_pago] where ([fecha]=?) I/O function: S, mode: 0, on file/table: ORD_PAGO index: 2 = Where ((FECHA = 11022015 00:00:00)) Use Index is 2 is (<<A4><0><0>) FECHA is (<<A4><0><0>) 22007 [Microsoft][SQL Server Native Client 10.0]Invalid time format The variable $procerrorcontext: "ERROR =-3·;" MNEM = < UIOSERR_EXCEPTIONAL > ·; DESCRIPTION = I/O error·; COMPONENT = XPROFIT_ORD·; PROCNAME = 4\READ·; TRIGGER = READ·; LINE = 1 " The variables $dberror and $dberrortext null value. Is it possible that I can help with these data? Thanks a lot.


    Author: parcaya (parcaya@compuamerica.com.ve)
  3. I've seen this before. The problem only occurs when using the SQL Server Native Client version 10.0 (for SQL Server 2008) or higher. When using the SQL Server 2005 Native Client or the SQL Server ODBC driver that is shipped with Windows then it's no problem to retrieve or store smalldatetime fields in SQL Server. And if I recall it correctly then the lab has looked at this issue years ago and it was concluded that this is a problem with the SQL Server Native Client drivers and not the Uniface MSS connector. I even can replicate the problem with the Microsoft ODBC Driver 11 for SQL Server (that was shipped with SQL Server 2014). It seems to be a bit odd that this error is present in so many different versions of the SQL Server Native Client and Microsoft ODBC Driver for SQL Server. Beats me. Hope this helps. Regards, Daniel


    Author: diseli (daniel.iseli@uniface.com)
  4. diseli said I've seen this before. The problem only occurs when using the SQL Server Native Client version 10.0 (for SQL Server 2008) or higher. When using the SQL Server 2005 Native Client or the SQL Server ODBC driver that is shipped with Windows then it's no problem to retrieve or store smalldatetime fields in SQL Server. And if I recall it correctly then the lab has looked at this issue years ago and it was concluded that this is a problem with the SQL Server Native Client drivers and not the Uniface MSS connector. I even can replicate the problem with the Microsoft ODBC Driver 11 for SQL Server (that was shipped with SQL Server 2014). It seems to be a bit odd that this error is present in so many different versions of the SQL Server Native Client and Microsoft ODBC Driver for SQL Server. Beats me. Hope this helps. Regards, Daniel  

    Hi, I am trying to find the “SQL Server 2005 Native Client” to test but could not find it. Do you know another workaround? Thanks Do you speak Spanish?


    Author: parcaya (parcaya@compuamerica.com.ve)
  5. parcaya said Hi, I am trying to find the “SQL Server 2005 Native Client” to test but could not find it. Do you know another workaround? Thanks

    When doing a quick search on (e.g.) Google then you should be able to find the following: > Feature Pack for Microsoft SQL Server 2005 - April 2006 The setup file sqlncli.msi is for the SQL Server 2005 Native Client. But, as mentioned, you could also use the SQL Server ODBC driver that is shipped with Windows. When you check the driver tab of the ODBC Data Source Administrator (make sure that you are using the correct version: 32- or 64-bit, depending on the Uniface version you are using) then you should find there a ODBC driver called "SQL Server" (with a version that matches the internal Windows Version; e.g. for Windows 10 it's version 10.00.xxxx).

    parcaya said Do you speak Spanish?  

     I'm afraid not. Only English, German and Dutch. Hope this helps. Daniel


    Author: diseli (daniel.iseli@uniface.com)