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

Resolved Best connection for MS SQL 2017 and 2019

  1. Advice
0
Votes
Undo
  1. Connie Dove
    Connie Dove
  2. PowerBuilder
  3. Tuesday, 3 May 2022 19:44 PM UTC

We have been using ODBC connections for our Powerbuilder applications for many years.  Until recently the applications were in Powerbuilder 10 or 12.5.  The applications had been running fine and did not seem to have issues.

Recently we have upgraded the applications to Powerbuilder 2019R3 and are now having issues with the applications running slow, or closing when doing an update.  

What is the best connection to use now against MS SQL 2017 and eventually 2019?

 

 

Here is what our 32 bit ODBC settings look like.

[HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"OFMABSQA2017"="SQL Server"

[HKEY_LOCAL_MACHINE\Software\Wow6432Node\ODBC\ODBC.INI\OFMABSQA2017]
"Driver"="sqlsrv32.dll"
"Description"="OFMABSQA2017"
"Server"="server1"
"Database"="ofmbilling_db"
"AnsiNPW"="No"

E-Charts Deployment
Powerbuilder + Git + change line-ending settings
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 3 May 2022 20:04 PM UTC
  2. PowerBuilder
  3. # 1

Hi Connie;

   The latest SS2017/2019 client DB driver connection software from Microsoft that SS uses is activated in the newer PB versions via this setting:

SQLCA.DBMS = "MSOLEDBSQL SQL Server"   // OR  SQLCA.DBMS = "MSO"     // for short

However, the ODBC SS DB driver is still well supported by MS & PB. So your performance issue is strange.

  I would also recommend the following ...

  1. Create a custom PB.INI using Notepad
  2. Add the following to the INI file ...
    • [Data Window]
      Accessibility=0
  3. Copy the new PB.ini file to the same folder where you PB App's EXE is deployed

HTH

Regards ... Chris

Comment
There are no comments made yet.
Connie Dove
Connie Dove Accepted Answer Pending Moderation
  1. Wednesday, 4 May 2022 16:04 PM UTC
  2. PowerBuilder
  3. # 2

Hi Chris,

  Thanks for the info.  I already had the pb.ini file in the folder with the exe.  

 

I am going to look into the MSOLEDBSQL to see if it makes a difference

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 4 May 2022 16:21 PM UTC
Hi Connie ... please let us know your findings with that driver.

The other suggestions are:

1) Use the App Profiler feature of the PB IDE to zero in on blocks of slow running code

2) Use the SQL Trace feature to zero in on poorly performing DML (from step#1).

HTH .. regards ... Chris
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 5 May 2022 07:41 AM UTC
Other driver you could try could be OLE DB. It's still there in PB 2019 R3 and could be a good alternative. We have been using it for a long time with good performance.
  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.