Hi, Dexter - 

Please allow me to call out some points regarding the MSOLEDBSQL client provider, in the event you may not be aware:

1. You must install the MSOLEDBSQL client provider using the .msi supplied in the download, according to Microsoft. Manual copying of the unpacked files will not work. The web page on the Microsoft web site where you downloaded the MSOLEDBSQL client from contains useful information about the installation. Please read through this info if you have not done so.

2. As Chris has stated, you can install both the 32-bit version and the 64-bit version, even on a 64-bit version of Windows. The bitness of the application that will be using the MSOLEDBSQL provider is the driving factor governing which bitness is needed. Since the PowerBuilder IDE is a 32-bit application, you need to install the 32-bit version of MSOLEDBSQL. Only if you will be creating a 64-bit PB application will you need the 64-bit version of MSOLEDBSQL with PowerBuilder. Other apps, however, such as SQL Server Management Studio (SSMS) may use/need the 64-bit version. Since both bitnesses can co-exist, I suggest you install both. You can also install both v18 and v19 of the MSOLEDBSQL provider on the same machine, as Chris indicated.

3. The only version of PB that can utilize MSOLEDBSQL v19 is PB 2022. Older PB versions (only as far back to when the MSO driver was first supported in PB 2019 R3) can only use v18.

If you have not already done so, I suggest you first create a Database Profile in PB that utilizes the MSOLEDBSQL provider, as you can quickly test the connection from the dialog window. Once you have a working DB profile, you can examine/copy the syntax to set SQLCA properties in PowerScript code from the dialog window. This is MUCH easier than repeatedly coding/trying different SQLCA property values in your application.

Finally, a word or two about helping us help you: Your statement "our application can't connect using the driver" does not give us any useful information that would help us determine what is causing your problem.

It really helps us when you include your SQLCA properties, particularly SQLCA.DBParm (mask out all sensitive values, such as passwords and server names, etc.) If you are getting error messages, please include them. Same for the SQLCode, SQLDBCode and SQLErrText properties of SQLCA after issuing the Connect statement. When trouble-shooting Database connectivity issues, it can be very helpful to know the DBMS vendor (SQL Server, in this case), the DBMS version (2019, in this case, as you clarified in a follow-up response), and for SQL Server, if you are connecting using Windows credentials or a SQL Server login/user.

We're here to help you, but we need you to help us, too.

Best regards, John

1. Load 64-bit MSOLEDBSQL version 19 or newer unless you are running a 32-bit version of Windows.

2. DBParm=“DATASOURCE=‘SERVERNAME’,PROVIDER=‘MSOLEDBSQL19’,TrustedConnection=1,PROVIDERSTRING=‘Database=DatabaseName’,Encrypt=0,TrustServerCertificate=1”

Replace SERVERNAME and DatabaseName with your values

After you get it working, you can try to set Encrypt=1

This is working for us.  I have installed for hundreds o Carillon users. The amount of misinformation available on this topic didn’t make it easy to figure out though.

these instructions are only for PB 2022.

Hi,

Windows version?

Hi Dexter,

What do you mean by "I tried 32bit Microsoft OLE DB Driver v.19 but its not supported with the client system."?  What is error message you are getting?

What is your connection string?

Did you copy runtime DLL pbmsoledbsql.dll?  This DLL is required to support the MSOLEDBSQL driver.

For a 32-bit app you should be installing the 32-bit driver (not 64-bit).  Driver and app bitness must match.

PB 2022 does support v19 of MSOLEDBSQL driver so the version is definitely not the problem.

Best regards,
Armeen

Hi Dexter ;

  What version & build of SS are you using?

Regards ... Chris