prNativeClient (deprecated) still loads before prMSOLEDB?

[FredS]

LocalFailOver failed on my Windows 7 test machine and that was my preferred way of testing..
Turns out it all works on Windows 10 but only on Windows 7 when I force the prMSOLEDB provider.

SQL Server Native Client

Important
The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

[Stellar]

Hi Fred!

Thanks for reaching us.

Please note that UniDAC will use one of the supported providers in such order: prNativeClient / prMSOLEDB / prSQL.
We did not prioritize the prMSOLEDB provider to prevent behavioral changes for existing users.
Please specify the version of MS SQL Server and the version of Microsoft OLE DB Driver for SQL Server you are using?

Regards,
Sergey,
Devart Team

[FredS]

Please specify the version of MS SQL Server and the version of Microsoft OLE DB Driver for SQL Server you are using?

MS SQL Server version made no diff, tested against 2008-2017..
MSOLEDB is v18.6 and the original Native was from MSSQL 2012, but I updated to match my Win10 and the same behavior.

Code: Select all

prNativeClient=11.0.6020.0(2012), 11.0.7462.6(2014SP3) 
prMSOLEDB=18.6.0000.0 // all works with this
prSQL=06.01.7601 // LocalFailOver works 

This was the error on Win7 using prNativeClient:

Code: Select all

exception class    : EUniError
exception message  : Object was open. Cannot execute command in current transaction context until previous opened DataSet (CursorType is ctDefaultResultSet) is fetched to end

and log data of the working Failover:

Code: Select all

EMSError: TCP Provider: An existing connection was forcibly closed by the remote host. 
Alert: Reconnection Attempt: 1, Thread: MAIN 
Info: Reconnected to : Microsoft SQL Server 

[Stellar]

Hi,

Thanks for your request.

To implement automatic reconnect, you can try to handle the OnConnectionLost event in TUniConnection.
To use the OnConnectionLost event handler, you should explicitly add the MemData unit to the 'uses' list and set the TCustomDAConnection.Options.LocalFailover property to True.

https://www.devart.com/unidac/docs/deva ... onlost.htm

Code: Select all

uses
  MemData;

var
  FReconnect: Boolean;

...

procedure Button1Click.Button1Click(Sender: TObject);
begin
  FReconnect := False;
  UniConnection1.Options.LocalFailover := True;
  UniConnection1.Connect;
end;

procedure TForm1.UniConnection1AfterConnect(Sender: TObject);
begin
  FReconnect := False;
end;

procedure TForm1.UniConnection1ConnectionLost(Sender: TObject;
  Component: TComponent; ConnLostCause: TConnLostCause;
  var RetryMode: TRetryMode);
begin
  FReconnect := True;
  RetryMode := TRetryMode.rmReconnect;
end;

procedure TForm1.UniConnection1Error(Sender: TObject; E: EDAError; var Fail: Boolean);
begin
  Fail := not FReconnect;
end;

Best regards,
Sergey

[FredS]

I have a working failover, which stopped working but only using the deprecated Native Client..

[FredS]

I should add that I don't expect you to fix this. Windows 7 may no longer be used enough to make that worthwhile but changing the order the providers are loaded would be a good way to solve issues like this.

I understand that you don't want to break old stuff, although I do seem to get a lot of breaking changes not marked as such :)

Maybe the best way would be to add a global var that can be set to change the order of loading during initialization.

[FredS]

Just had an error with Server 2012 using Native on Azure against a 2017 SQL Server.

Code: Select all

EUniError: Database 'PA' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Direct worked fine.. so did MSOLEDB..

[Stellar]

Hi,

Kindly be informed that if you want to use Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL), then you can explicitly specify the required provider for the specific Provider option.

Code: Select all

UniConnection1.SpecificOptions.Values['Provider'] := 'prMSOLEDB';

Best regards,
Sergey

[FredS]

then you can explicitly specify

Right, the point of that post was to make you aware that a Server which had no issues a month or so ago is now coming up with errors that would not make one think its the fault of prNative..

So first Windows 7 with failover, now Server 2012.. yet UniDAC still loads DEPRECATED prNative..

I ran theses tests during app start loading: Native, Direct, MSOLEDB then Native again.. both Native runs had the same error..

What I should have done and will do the next time I start up the Azure Domain Forest is to test current release versions built with UniDAC prAUTO and see what happens..

[Stellar]

Hi Fred,

As we said earlier, when using the prAuto provider, UniDAC checks for the presence of an installed MS SQL Native Client. If the provider is installed, then UniDAC uses it.

The next check is whether the prMSOLEDB (Microsoft OLE DB Driver for SQL Server) provider is installed. We do not plan to change the sequence, as we do not have requests from other users.

You can set the required provider in the connection settings.

Best regards,
Sergey,