1. Suresh Thoduvail
  2. PowerBuilder
  3. Wednesday, 15 March 2023 00:13 AM UTC

Hi Experts,

******************************************************************

Database Versions: Microsoft SQL Server 2014/Microsoft SQL Server 2019 

******************************************************************

I have upgraded my existing application from PowerBuilder from PB 2021 (Build 1509) to PB 2022 (Build 1892).

My application was able to run/connect to the database earlier from PB 2021 with the below parameters:

ini file:

DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='SERVER\MY_DB15A,1404',PBCatalogOwner='dbo',DelimitIdentifier='No',PBTrimCatCharColumns='YES',PBTrimCharColumns='YES',PROVIDERSTRING='database=MY_DATABASE;APP=MYAPP-OLEDB',StaticBind='No'"

When I connect to the application using the same ini parameters from PB 2022, I get the error message --> "DBMS OLE DB is not supported in your current installation."

I have read in earlier Appeon community questions that the x64 version of PowerBuilder 2022 does not support OLEDB or Appeon has not included the OLE DB Database Driver in 64 bit version of PB 2022.

 

Please advise:

1. what are my best options to connect to MS SQL Server 2014 and 2019? 

2. where can I download the drivers necessary for this?

Thanks very much in Advance!

-Suresh

 

 

Accepted Answer
Roland Smith Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 22:59 PM UTC
  2. PowerBuilder
  3. # Permalink
Download version 19.2 instead. 19.3 has a hard time with the VC++ Runtimes.
Comment
There are no comments made yet.
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 02:02 AM UTC
  2. PowerBuilder
  3. # 1
Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 05:41 AM UTC
  2. PowerBuilder
  3. # 2

Hi, Suresh - 

Here is what I use in a Database Profile to connect to The Microsoft-supplied AdventureWorksLT 2019 (demo) database for SQL Server, running locally on my PC using SQL Server 2019 Express:

// Profile AdventureWorksLT 2019
SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.ServerName = "xxxxxxxxxxxxxxx\SQLEXPRESS2019"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='AdventureWorksLT2019',TrustedConnection=1,TrimSpaces=1,DateFormat='mm/dd/yyyy',DateTimeFormat='mm/dd/yyyy hh:mm:ss',TimeFormat='hh:mm:ss'"

You'll need to download the MSOLEDBSQL client from Microsoft and install it with the downloaded MSI. Here is a link to the latest version, which works with PB2022, but not older versions:

    https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

Note there are separate downloads/installs for 32-bit (x86) and 64-bit versions. Both can be installed safely on your machine.

Also note you must install it using the .msi, as copying the unpacked files does not work.

HTH, John

Comment
There are no comments made yet.
Suresh Thoduvail Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 14:28 PM UTC
  2. PowerBuilder
  3. # 3

Thank you all for the right direction/advise.

I have downloaded the msoledbsql.msi from the Microsoft website.

I am still struggling to install the msoledbsql.msi, it complains that I do not have C++ Redistributable for Visual Studio 2022, but it has been clearly downloaded and installed.

Not sure what to do next. Any suggestions greatly appreciated.

Thanks in Advance

Attachments (2)
Comment
  1. Suresh Thoduvail
  2. Wednesday, 15 March 2023 21:14 PM UTC
Successfully installed msoledbsql version 19.2 (picture attached).



No luck connecting PowerBuilder 2022 to the database using provider='msoledbsql'



Interestingly PowerBuilder 2021 connected to the database using provider='msoledbsql'





Any advise?









  1. Helpful
There are no comments made yet.
Suresh Thoduvail Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 21:15 PM UTC
  2. PowerBuilder
  3. # 4

Attached picture of MSOLEDB Driver 19.2

Attachments (1)
Comment
  1. Armeen Mazda @Appeon
  2. Wednesday, 15 March 2023 21:59 PM UTC
Are you sure that is 19.2 version? By default, Microsoft points you to 19.3 download link. Here is download link for 19.2 x64 version: https://go.microsoft.com/fwlink/?linkid=2212594
  1. Helpful 2
There are no comments made yet.
Suresh Thoduvail Accepted Answer Pending Moderation
  1. Wednesday, 15 March 2023 22:57 PM UTC
  2. PowerBuilder
  3. # 5

Thank you all for your time and help. I think I have finally resolved the issue.

In a rush to get this issue resolved, I was blindsided in having the DBMS Parameter set wrong in the ini file.

Earlier my --> DBMS = "OLE DB"

I changed it to --> DBMS = "MSOLEDBSQL SQL Server"

and the provider string worked with 'MSOLEDBSQL'

DBParm = "PROVIDER='MSOLEDBSQL',DATASOURCE='D-MIS-DBS011.myserver.ca\DEVM15A,1404',PBCatalogOwner='dbo',DelimitIdentifier='No',PBTrimCatCharColumns='YES',PBTrimCharColumns='YES',PROVIDERSTRING='database=APP_DEV_A;APP=MYAPP-MSOLEDBSQL',StaticBind='No'"

 

Thanks to Armeen, Roland, and John for all of your advise and it has contributed to the solution.

The clue to see that my DBMS Parameter was set wrong came from John's answer.

Every one of you have helped me in resolving this issue, but I have to mark one as my answer and it's a tough choice, so I Choose Roland's answer as if I would have not downloaded the lower version of MSOLEDBSQL 19.2, I would not have been able to install the Driver and hence connected to the Database.

 

Comment
  1. Armeen Mazda @Appeon
  2. Thursday, 16 March 2023 00:32 AM UTC
Thanks for sharing the solution!
  1. Helpful 1
  1. John Fauss
  2. Thursday, 16 March 2023 01:29 AM UTC
@Suresh - I'm glad you were able to get this resolved. Congratulations!
  1. Helpful 1
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.