FireDAC, New OLE DB Driver, and SQL Server

[BruceTTTT 7]

Is there any way to force FireDAC to use the new OLE DB Driver (not the older Provider) in the msoledbsql file? Discussed here:

 

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/microsoft-odbc-driver-for-sql-server-on-windows?view=sql-server-2017

https://support.microsoft.com/en-us/office/connect-access-to-sql-server-050d88f3-b2d6-4e76-b6f9-f3c556f139ea
 

The native client drivers are deprecated, and I can use the related ODBC 17 driver, but it's slower. ADO (dbGo) with the new OLE DB driver is the fastest, but I'd like to switch to FireDAC and I'm forced to use the ODBC 17 driver if I do that because of the incompatibilities of the native SQL driver (that FireDAC uses) and different SQL versions.

[H05 0]

@BruceTTTT Did you every figure this one out? I'm looking at testing the SQL Server OLE DB 19 driver but it's always defaulting to SQL Server Native Client 11 

• https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

Edited April 16, 2022 by H05

[BruceTTTT 7]

Emb decided not to address it and closed the QualityCentral request I posted (sorry, don't remember the number, but you can look it up). They said that FireDAC for SQL Server "uses ODBC" and left it at that. Very disappointing to me.

[H05 0]

Thanks @BruceTTTT. Found it https://quality.embarcadero.com/browse/RSP-32494 That's really too bad. Once MS undeprecated the OLEDB approach I had high hopes for FireDac performance again

[BruceTTTT 7]

I was starting to like FireDAC and was beginning recoding all my apps from ADO to FireDAC, then this happened. I hate ODBC, so I'm still sticking with ADO.

[H05 0]

For what it's worth I ran several thousand queries across three variations and found all about the same performance (or at least within the tolerance that we're happy with):

• ADO + OLEDB;
• FireDAC + ODBC 18; and
• FireDAC + SQL Native Client 11.0

 

 

I'd like the option to replace SQL Native Client though as our clients tend to balk at installing deprecated drivers.

We moved from BDE to FireDAC so we're pot committed 😕

[BruceTTTT 7]

I tested this many times as well (see the above QC ticket), ODBC was slower loading. And there's also the configuration of the ODBC driver on each client that you must do sometimes. The main problem I have with the Native Client is that it's keyed to the version of SQL Server. We have multiple versions on our servers, and there's no on Native Client that can handle them all. The nice thing about ADO + OLEDB is that this is not an issue. When using the newest OLEDB driver from Microsoft and ADO, all of these issues are resolved. It's fast, one driver works on everything. But of course, it doesn't have all of FireDAC's new stuff.

 

I don't know why this is such a big deal for Emb. Just implement this driver. SQL Server is everywhere, why wouldn't you want to implement the best solution?

[Anders Melander 2023]

On 4/17/2022 at 4:48 AM, BruceTTTT said:

I hate ODBC, so I'm still sticking with ADO.

Um... you aren't using ODBC data sources via FireDAC, are you?

ODBC is just the API FireDAC uses to talk to the driver. From your perspective there's nothing ODBC about it - the API could be anything.

 

1 hour ago, BruceTTTT said:

SQL Server is everywhere, why wouldn't you want to implement the best solution?

Maybe they did 🙂

We've just completed replacing MSSQL via ADO with FireDAC and for us it works fine.

 

We had to update the client library to a newer version on a couple of clients but apart from that it's faster, better. I think the oldest SQL server version we connect to is 2014/v12.