Jump to content
Sign in to follow this  
Martyn Spencer

Unreliable connection to remote MS SQL Server database

Recommended Posts

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

 

Share this post


Link to post

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.

Share this post


Link to post

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.

Share this post


Link to post

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.

 

 

 

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×