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

Resolved SNC (SQL Native Client), db compatibility level and datetime field in PK

  1. Issue
0
Votes
Undo
  1. Aliaksei Hlinski
    Aliaksei Hlinski
  2. PowerBuilder
  3. Thursday, 14 January 2021 08:00 AM UTC

Hello,

We have discovered the following: using SNC (SQL Native Client, provider SQLNCLI11 of course), so when a table has datetime column in primary key and database compatibility level is set to, for example, 130 (SQL Server 2016), datawindow based on such table won't update the table, because when I look in SQL Server Profiler I see that datetime2 is used

exec sp_executesql N'UPDATE TIMEREG_SUBTABLE SET CLOCK_STOP = @P1 WHERE CLOCK_ID = @P2 AND CLOCK_ROW = @P3 AND CLOCK_START = @P4 AND CLOCK_STOP = @P5 AND CLOCK_COMMENT IS NULL ',N'@P1 datetime2(7),@P2 int,@P3 int,@P4 datetime2(7),@P5 datetime2(7)','2020-11-16 18:00:00',16164,1,'2020-11-16 16:26:38.7770000','2020-11-16 16:27:26.3500000'

and when data values from string are converted to actual datetime data they don't match, for example '2020-11-16 16:26:38.7770000' is converted to '2020-11-16 16:26:38.7766667'.

There is no such problem of course when compatibility level is 100 (SQL Server 2008), but it is not an option in my case. Is there a way to control this behavior of SNC driver?

PowerBuilder 2017 R3 build 1858.

Thanks,
Aliaksei.

  1. DataWindow
  2. SQL Server
SendPostRequest question
Kill a running SharedObject
Responses (2)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 14 January 2021 14:37 PM UTC
  2. PowerBuilder
  3. # 1

Greetings, Aliaksei -

Have you tried setting the DateTimeFormat option in the transaction object's DBParm property prior to connecting to the database? Our app uses the SQL Server smalldatetime datatype instead of datetime (we only need time resolution to the nearest minute), so we use the following DBPArm options:

SQLCA.DBMS = "SNC"
SQLCA.DBParm = "Database='xxxx',Provider='SQLNCLI11',TrustedConnection=1,TrimSpaces=1,DateFormat='mm/dd/yyyy',TimeFormat='hh:mm',DateTimeFormat='mm/dd/yyyy hh:mm'"

This works for us in SQL Server 2012, 2014, 2016 & 2017 (we have not migrated any customers to SS 2019 yet). We use smalldatetime columns in many tables, and in some cases as part of the table's primary key with no problems.

HTH

Regards, John

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 14 January 2021 13:46 PM UTC
  2. PowerBuilder
  3. # 2

Hi Aliaksei ;

   The DateTime2 data type is not supported by PB. In older SS versions, it would ignore the extra digits in the thousands of a second part of the DT columns. So you could get away with DT2 as internally, SS would treat it as DT.

    However, newer SS versions are sensitive to this higher accuracy. My big question though is why are you using a DT column as a primary key?

Regards ... Chris

Comment
Aliaksei Hlinski
  1. Aliaksei Hlinski
  2. Thursday, 14 January 2021 14:09 PM UTC
Thank you for reply. I know datetime column in PK is bad practice and the problem is easy to solve by changing table PK and this is something to be done, but at the moment I'm more concerned if there is a way to make SNC driver work properly when compatibility level is 130. BTW I don't use datetime2 datatype, my column is datetime datatype, it works just fine, so I will simplify the problem:

- datawindow is built on table which has datetime column in PK

- this dw updates data just fine when db compatibility level is 100 (SQL2008), but when compatibility level is 130 (SQL2016) update statement produced by dw doesn't update the data, because internally (on SNC driver level I think) there is string-to-datetime2 conversion.

Is there a way to control that?



Thanks,

Aliaksei.
  1. Helpful
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.