Unreliable connection to remote MS SQL Server database

[Martyn Spencer 0]

Hi. I am not overly familiar with SQL server but need to extract some data on a reasonably regular basis. I planned to do this using FireDAC. The providers of my client's CRM have given me read only access to their database. Connecting to the server using FireDAC is proving inconsistent. It will connect on occasion and then not on others. Checking the FireDAC log, I see the below information and wondered if it is likely to be related to the problem - specifically the driver version being 10 and the server 14. Interestingly, FireDAC appears to find a driver version 11 but does not use it. Is anyone able to assist?

 

Thanks.

 

Checking session ...
  Warning: The client [10.0.0.0.0] and server [14.0.0.0.0] major versions difference > 1.
  Warning: SQL Server 2016 and compatibility level >= 130 may lead to DATETIME comparision failure.
Current catalog = 
Current schema = dbo
Driver name = SQLSRV32.DLL
Driver version = 10.00.17134
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 14.00.3048

 

[Attila Kovacs 631]

sqlsrv32.dll is ODBC driver, sqlncli11.dll is native driver, there must be a setting in FD how to connect. Unidac hat at least 5 different methods.

[Martyn Spencer 0]

I have done some testing and the unreliability is server-side. I would still be quite happy to hear the best way to configure FireDAC to connect to SQL server, ideally directly if possible.

[Lars Fosdal 1793]

Somewhat related: SQLNCLI is deprecated.  MSOLEDBSQL is the new best practice.

As for unreliable connections - some servers disconnects pretty quick, so we have a wrapper that handles this and reconnects as required.

 

 

 

[Attila Kovacs 631]

I'd also check the host os's event viewer. It could be also a licensing issue.

[Martyn Spencer 0]

Thanks for the feedback.

[Lars Fosdal 1793]

FYI, @Martyn Spencer, it seems that the new MSOLEDBSQL drivers are as fast as SQLNCLI.  See other thread above for how to select a specific driver.

[Martyn Spencer 0]

Thanks Lars.