• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Re: Migrate DBMS = "SNC SQL Native Client(OLE DB)" to "MSOLEDBSQL SQL Server"

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

Resolved Re: Migrate DBMS = "SNC SQL Native Client(OLE DB)" to "MSOLEDBSQL SQL Server"

  1. How-to
0
Votes
Undo
  1. sachidhanantham ramalingam
    sachidhanantham ramalingam
  2. PowerBuilder
  3. Tuesday, 14 June 2022 09:47 AM UTC

Hi,

We have migrated PB 2017 to PB 2019 R3 and need to change the DBMS = "SNC SQL Native Client(OLE DB)" to "MSOLEDBSQL SQL Server",

So, we want your suggestion that do we need to include "Provider = 'MSOLEDBSQL'" in SQLCA.DBParm string or not, since previously we haven't included any "Provider" information in SQLCA.DBParm string.

Our existing SQLCA.DBParm string:
------------------------------------------
SQLCA.DBParm = "DBTextLimit='32000',TrimSpaces=1,ProviderString='MARS Connection=False',"+ls_Host+",Database='"+SQLCA.Database+"',FailoverPartner='"+ls_FailOverServer+"',Initial Catalog='"+SQLCA.Database+"'"

Thanks,
--Sachi...

 

Database Painter Tables getting blocked by client
Number of rows in a report
Responses (3)
Tracy Huang @Appeon
Tracy Huang @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 June 2022 08:48 AM UTC
  2. PowerBuilder
  3. # 1

Hi Sachi,

To use MSOLEDBSQL 18.x, there is no need to set Provider, it is the default value when Provider is not set.

SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

 

To use MSOLEDBSQL 19, set Provider='MSOLEDBSQL19'.

MSOLEDBSQL 19 is supported starting from version 2022.

SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='MSOLEDBSQL19'"

 

You can verify this in the Database Profiles dialog: select MSOLEDBSQL SQL Server from the Installed Database Interfaces treeview, click the New button; in the Connection tab, set Provider to MSOLEDBSQL (if using 18.x), view the connection string that is automatically generated in the Preview tab, and then set Provider to MSOLEDBSQL19 and view the connection string again.

 

By the way, OLE DB Provider for SQL Server (SQLOLEDB) is not maintained any more; use Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) (the new generation of OLE DB provider for SQL Server) instead of OLE DB Provider for SQL Server (SQLOLEDB).

 

Regards

Tracy

Comment
There are no comments made yet.
Julie Jiang @Appeon
Julie Jiang @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 June 2022 08:44 AM UTC
  2. PowerBuilder
  3. # 2

Hi Sachi- et al,

Sorry for the confusion caused.  Today I further confirmed with the engineering team and found the following information.

1) With MSOLEDBSQL18, you need not specify the provider in DBParm. For example:

SQLCA.DBMS = "MSOLEDBSQL SQL Server"

SQLCA.AutoCommit = False

SQLCA.DBParm = ""

2) With MSOLEDBSQL19 (note that we start to support this version from PB 2022), to differentiate with MSOLEDBSQL18, you need specify the provider, like below:

SQLCA.DBMS = "MSOLEDBSQL SQL Server"

SQLCA.AutoCommit = False

SQLCA.DBParm = "Provider='MSOLEDBSQL19'"

Best regards, Julie

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 14 June 2022 13:20 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Sachi -

This DBParm setting is used to identify which "provider", or client-interface is to be used with the selected PB database driver. For example, with SNC, there are multiple supported providers, such as SQLNCLI, SQLNCLI10 and SQLNCLI11. This (so far) is not the case with the MSO database driver. According to the PB documentation:

   https://docs.appeon.com/pb2019r3/connection_reference/ch01s01.html#Provider

the correct value to use with the MSO database driver is "SQLOLEDB", not "MSOLESQLDB", should you choose to include this DBParm option.

Regards, John

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 14 June 2022 14:01 PM UTC
AFAIK: Once you set SQLCA.DBMS "MSO" ... the DBParm value should be ignored.
  1. Helpful
John Fauss
  1. John Fauss
  2. Tuesday, 14 June 2022 14:45 PM UTC
Thanks, Chris! I agree that the "Provider" DBParm setting should not be included if SQLCA.DBMS="MSO", since there is currently but only one provider.



YADE (Yet Another Documentation Error).
  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Monday, 6 February 2023 12:23 PM UTC
We are migrating from SNC SQL Native Client(OLE DB) to MSOLEDBSQL SQL Server and also moving from 2017R3 to PB2022. The answer from Tracy and Julie is 100% correct based on my tests. I found their explanation much clearer than the documentation.



This is the syntax created for the profiles in the database profile setup.

Version 19 Syntax



// Profile matrixOLE

SQLCA.DBMS = "MSOLEDBSQL SQL Server"

SQLCA.LogPass = <*******>

SQLCA.ServerName = "PBS41"

SQLCA.LogId = "sa"

SQLCA.AutoCommit = True

SQLCA.DBParm = "Database='loadmax',Identity='SCOPE_IDENTITY()',Provider='MSOLEDBSQL19' "



Version 18 Syntax

// Profile MatrixOLE18

SQLCA.DBMS = "MSOLEDBSQL SQL Server"

SQLCA.LogPass = <*******>

SQLCA.ServerName = "pbs41"

SQLCA.LogId = "sa"

SQLCA.AutoCommit = True

SQLCA.DBParm = "Database='Loadmax',Identity='SCOPE_IDENTITY()'"



I used the generated Syntax to run an application



Version 18 syntax will not work if only version 19 is installed.

Version 19 syntax will work if either driver Version 18 or 19 is installed - it uses version 18 if no version 19 installed.



With SNC SQL Native Client(OLE DB), provider could be left empty, and connection would use use the installed version rather than just a specified version.

It might be preferable if MSOLEDBSQL SQL Server Driver worked like this to avoid having to provide a specific version.

  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.