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

Resolved Migrating from SAP ASE -> MS SQL, which DB driver to choose from?

  1. Advice
0
Votes
Undo
  1. Pieter Coene
    Pieter Coene
  2. PowerBuilder
  3. Thursday, 28 September 2023 07:57 AM UTC

Hi,

After developing for more than 25 years on a Sybase (SAP ASE) database, we are migrating to MS SQL. We are using the native ASE interface for our database connection. We have some large Powerbuilder applications that rely heavily on this database. However, most of communication is done though stored procedures (data retrieval and insert/update/delete).

I'm just starting to look how we can connect our Powerbuilder applications to the MS SQL database but i'm not sure which to choose from. Reading the docs, it seems that i have these options:

  • Standard Database Interface -> ADO.Net
  • Native Database Interface ->
    • MSOLEDBSQL SQL Server
    • SNC SQL Native Client

The SNC looks outdated (don't know for sure, just by looking at the supported MS SQL versions). Besides the performance difference (i read somewhere that the standard database interfaces are slower than the native, but i'm not sure if we will notice this), what are the main differences choosing between the ADO.Net or the MSOLEDBSQL SQL Server?

Or maybe in other words -> since we have no legacy here, which one is more future proof?

Greetz
Pieter

  1. Database Driver
  2. SQL Server
SVN: add Tag from IDE and why SVN Icons not availa...
Anyone have advice on collecting electronic signat...
Responses (5)
Trevor Holyoak
Trevor Holyoak Accepted Answer Pending Moderation
  1. Thursday, 30 November 2023 00:08 AM UTC
  2. PowerBuilder
  3. # 1

I'm coming to this thread a little late, but we've got the same question. It seems that the consensus here is to use the MSOLEDBSQL driver. However, in the recent Elevate demonstrations, everyone seemed to be using ADO.NET. Is there a reason for that? Which is easiest to deploy? (We're going to be switching from SQL Anywhere to SQL Server.)

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Thursday, 30 November 2023 00:43 AM UTC
Generally, MSOLEDBSQL is what we recommend unless there is specific technical requirement to use ADO.NET driver. Also, the MSOLEDBSQL driver should be most comparable to the now-deprecated SQL Server Native Client.
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Monday, 4 December 2023 18:51 PM UTC
When we migrated from PB 2019 to PB 2022, we started using 'Microsoft OLE DB Driver for SQL Server' version 19.2 instead of Native Client and for ODBC connections we now use 'Microsoft ODBC Driver 18 for SQL Server' version 18.2 instead of the Native Client ODBC driver.
  1. Helpful
There are no comments made yet.
Pieter Coene
Pieter Coene Accepted Answer Pending Moderation
  1. Friday, 29 September 2023 06:41 AM UTC
  2. PowerBuilder
  3. # 2

Hi,

Thx for the answers.

I forgot indeed to mention our PB version. We are currently using PB 2019 R3 (build 2797). We do have plans in the near future to switch to PB2022, so then i can use the latest version of the MSOLEDBSQL driver. 

I just did a quick test and the connection works fine. Funny thing i noticed is that an raiserror from a stored procedure is shown (meaning the SQLErrText property of the transaction object) very different for each type of connection i tried (ADO.Net with the MSOLEDBSQL provider vs ADO.Net with the System.Data.SqlClient vs Native MSOLEDBSQL).

i think i will focus on the native MSOLEDBSQL driver.

Thx for the help! 

Greetz
Pieter

Comment
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 28 September 2023 14:55 PM UTC
  2. PowerBuilder
  3. # 3

We switched to MSOLEDBSQL from SNC when we moved from PB 2019 to PB 2022. Microsoft dropped support for SNC over 5 years ago so that shouldn't be considered.

Your connection should look something like this:

sqlca.DBMS       = "MSOLEDBSQL SQL Server"
sqlca.ServerName = "TheServerName"
sqlca.LogId      = "myuserid"
sqlca.LogPass    = "mypassword"
sqlca.DBParm     = "Provider='MSOLEDBSQL19',Database='TheDatabaseName',AppName='TheApplication'"
sqlca.AutoCommit = False
Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 28 September 2023 13:16 PM UTC
  2. PowerBuilder
  3. # 4

Hi, Pieter -

I, too, suggest you consider using MSOLEDBSQL.

You did not explicitly state which version of PB you are using (always a good idea when asking a question), so please be aware that the version 19.x of the MSOLEDBSQL driver from Microsoft is only supported beginning in PB 2022. Earlier versions of PB that support the MSO database client must use MSOLEDBSQL v18.x.

Good luck with your database migration!

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 28 September 2023 12:40 PM UTC
  2. PowerBuilder
  3. # 5

Hi Pieter;

  The MSOLEDBSQL db driver is the latest SS client from Microsoft and actually replaces the older SNC driver.  I would use that one IMHO.  HTH

Regards .... Chris 

Comment
Brett Weaver
  1. Brett Weaver
  2. Friday, 29 September 2023 04:49 AM UTC
I have used the MSOLEDBSQL19 driver for new applications for the last year or so with nothing but good results. Be aware of date formatting if you decide to go to datetime2 and nvarchar as part of your conversion.
  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.
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.