MS SQL Server - IsolationLevel problem

[chkaufmann]

I have a backup task and to run it I open a separate connection with
IsolationLevel := ilSnapShot
Readonly := True

So far this works fine on my workstation, including MS SQL Server. But on other computers the application fails when I try to open the connection:

0149eab4 Logo.exe MSClassesUni 1188 TMSSQLConnection.Connect
012eb941 Logo.exe DBAccess 4065 TCustomDAConnection.DoConnect
0134253f Logo.exe Uni 1791 TUniConnection.DoConnect
012ebc97 Logo.exe DBAccess 4181 TCustomDAConnection.PerformConnect
012eeb08 Logo.exe DBAccess 5184 TCustomDAConnection.SetConnected
005d8be0 Logo.exe Data.DB TCustomConnection.Open
01427f3a Logo.exe BSDBUniDACImp 331 TBSUniConnection.InternalOpen

This is the error message. Maybe not 100% correct because the original was in german:
Errors in a multi-step OLE DB operation. Check each OLE DB status value, if available. Data has not been processed. {C8B522C6-5CF3-11CE-ade5-00aa0044773d} [0]: = $. 2 PropID: = 190th


The only difference between mine and other workstations is, that I have installed the MS SQL Server client on mine. Is there any additional property I have to set in the TUniConnection to make Snapshot isolation work without the client installed?

cu Christian

[azyk]

This problem is caused by the fact that the standard OLEDB provider does not support the ilSnapShot isolation level. To solve the problem you should use the SQL Native Client provider. For this, in connection settings set the option TUniConnection.SpecificOptions.Values['OLEDBProvider'] to 'prNativeClient'. SQL Native Client provider must be installed on the client PC.

[chkaufmann]

Native client is not an option in my case, because then customers need to do an install of it on their clients.

I tried "direct mode" and this seem to work. What I didn't find is a list of disadvantages when I use "direct mode". All my clients are Windows (7, 8, 10).

cu Christian

[azyk]

SDAC supports connection to SQL Server in Direct Mode via TCP/IP in the same way as SQL Native Client or OLEDB providers do. So Direct Mode has no disadvantages in comparison to usage of the above providers.

Direct Mode has just the following limitations:
- Windows Authentication is not supported;
- Connection using TCP/IP protocol only;

[chkaufmann]

So you could check for this option:

UniConnection.SpecificOptions.Values['Authentication'] := 'auWindows';

and don't use direct mode in this case.

Now I do the check in my code.

cu Christian

[azyk]

For the described case, we can't change implicitly (without notifying user) the type of the provider specified by user.