Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Datetime issue with SQL 2016

  1. Issue
0
Votes
Undo
  1. Daniela Jimenez
    Daniela Jimenez
  2. PowerBuilder
  3. Wednesday, 15 April 2020 23:33 PM UTC

Hi,

We recently started supporting SQL Server 2016 and SQL Native Client 11.0 in our application, but we are running into some issues with DateTime and Time fields.

In the DB we have several Datetime columns, but since we added the compliance with SQL 2016 when we modify a datetime field it doesn't get updated. We verified the DB after upgrading to SQL 2016 and the fields remained the same (datetime columns remain as datetime and were not changed to datetime2 as someone pointed as a possible reason). If we change the compatibility flag on our database to SQL 2014 then the data is updated correctly.

Any one knows the reason of this behavior and how to solve it?

 

Thanks,

Can't set the background colour of a dw text contr...
ERROR Updating New Columns - SQL SERVER 2016
Responses (4)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 01:49 AM UTC
  2. PowerBuilder
  3. # 1

Hi,

1) Are you doing an embedded UPDATE ... SET .... statement or an Update() on a datawindow or datastore?

2) Do you get any error message?

3) Can you make a small test table with a field which you do define as datetime2?

4) If it works with datetime2, than maybe you won't have to set the compatibility mode. (since you said it worked for a normal datetime, when using that).

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 17 April 2020 20:08 PM UTC
Hola Daniela,

Wouldn't it be you might be missing a COMMIT; after the updates? Because if you don't get an error message on the update ....

  1. Helpful
Daniela Jimenez
  1. Daniela Jimenez
  2. Friday, 17 April 2020 22:12 PM UTC
No it is not, the code is old, and if I change the compatibility flag in SQL Server the update works correctly.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 18 April 2020 00:57 AM UTC
Ok, that's great. Maybe you should report this as a bug? Appeon is proud to support the latest versions of Database, but this one seems clear then.
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 19 April 2020 00:00 AM UTC
  2. PowerBuilder
  3. # 2

MSSQL uses 7 decimals on DateTime2 while PowerBuilder's precision is 6 decimals if I'm not mistaking.
You may have those 10-millionth's fractional mismatch.

If you set DisableBind=1 and capture SQL statements in SQLPreview event (DW,DS, or transaction) you can capture the "failing" SQL. Those are excellent test cases in MS SQL Mgmt Studio to see what actually happens.

@@ROWCOUNT should tell you same value as PB transaction's SQLNRows. When SQLNRows = 0 for UPDATE from DW you will see error = row changed between retrieve and update.

HTH /Michael

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 13 October 2020 19:56 PM UTC
Hi Michael;

Correct. It's not in the DB Driver(s) that PB uses, its an internal PB issue in the way that it handles (or in this case does not handle) the extra precision for the DT2 data type.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Monday, 12 October 2020 16:54 PM UTC
  2. PowerBuilder
  3. # 3

I suggest testing out the new OLE DB driver for SQL Server that is in PB 2019 R3 and see if that solves your issue.  Beta version of PB 2019 R3 is currently available, and there is a good webinar about this new driver.

Comment
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 12 October 2020 23:15 PM UTC
  2. PowerBuilder
  3. # 4

Following up on Miguel's statement:

If you are updating with a DW, please ensure that the ROWS / UPDATE PROPRTIES window shows that the datetime field is selected to be updated.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.