1. Scott Gorke
  2. PowerBuilder
  3. Thursday, 23 June 2022 15:40 PM UTC

Hi,

 

Powerbuilder 2019 R3

MS SQL Server

INI file excerpt:

[SQLServer]
LogID=<SQLuser>
Logpass=<SQLpassword>

[Database]
DBMS=SNC SQL Native Client(OLE DB)
Database=<DBname>
ServerName=<SQL server instance>
DBParm=Provider='SQLNCLI10',Database='<DBname>',DBTextLimit='32766', ProviderString='OLE DB Services=-4; MARS Connection=False', Identity='SCOPE_IDENTITY()', TRIMSPACES=1, RecheckRows=1

 

We have run into instances in which PB and/or SQL Native Client tries to insert a row into a table - the record includes a datetime column, that for some reason sets the datetime with a 6-digit millisecond. (EX: insert into mytable (col1, col2) values (<ID value>, '2022-06-23 12:34:56:357777').

 

If the datetime only has 3 digit milliseconds, no problem.

 

We could write a PB fix to truncate the milliseconds down to 3 digits, but that would require compiling the app, testing, delivering, etc. which would take weeks. To save time, is there a setting within MS SQL Server to automatically truncate the milliseconds as we wish?

 

Thanks in advance,
Scott

Accepted Answer
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 23 June 2022 18:31 PM UTC
  2. PowerBuilder
  3. # Permalink

Maybe you can set the datetimeformat in the SQLCA dbparm to be limited to fff (3 miliseconds)?

regards.

Comment
  1. Miguel Leeuwe
  2. Friday, 24 June 2022 04:51 AM UTC
In Oracle you can do an ALTER SESSION to set the timstamp and date(time) precision for milliseconds. I don't know if there's something similar for sql server.
  1. Helpful
  1. Scott Gorke
  2. Wednesday, 17 August 2022 14:15 PM UTC
Thanks Miguel. The SQLCA DBParm solution was considered, but because a client was complaining about the issue, we wrote a fix in the Powerbuilder code to truncate the milliseconds to three digits. For the unhappy client, this was the fastest fix (our app already trims to 3-digit milliseconds in several other areas, and our client found 2 other areas that we missed.).
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 24 June 2022 17:35 PM UTC
  2. PowerBuilder
  3. # 1
Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 24 June 2022 17:25 PM UTC
  2. PowerBuilder
  3. # 2

In SQL Server DATETIME is 3 digits while DATETIME2 is 7 digits.

The PowerScript data types Time & DateTime has 6 digits. I'm guessing the DataWindow columns are the same.

The PowerScript Now() function only returns 3 digits.

The quick fix would be to change DATETIME2 columns to DATETIME2(3).

 

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.
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.