SQL-Server OleDB TransactIsolationLevel Read../None

[nlanger]

Hello,

I have a problem with transactions and OleDB on SQL Server 2016. I execute many SQL commands in one transaction, but most of them don't.

If I start the transaction with the connection open, it works until COMMIT. When switching back the TransactIsolationLevel, an error occurs when I
use the "OleDB" protocol. With the "ado" protocol it works without errors.

...
ZConn.Protocol:='OleDB';
ZConn.AutoCommit:=True;
ZConn.Database:=
'MSOLEDBSQL.1;Persist Security Info=True;User ID=NLsoft;Data Source=NLwin8;Initial Catalog=NL001;MARS Connection=True';
ZConn.Connect;
...
// SQL select & update ...
...
ZConn.TransactIsolationLevel:=tiReadCommitted;
ZConn.StartTransaction;
...
// SQL select & update ...
...
ZConn.Commit;
ZConn.TransactIsolationLevel:=tiNone; // no Error with Protocol:='ado' => Error: Protocol:='OleDB' (see attachement)
...

What could be the reason?

[EgonHugeist]

Hello Norbert,

according the TransactionIsolationLevel:
tiNone will be mapped to ISOLATIONLEVEL_CHAOS it's documented by Microsoft. SQLServer simply doesn't support that isolation level.

Is there a reason why you'r doing that? What do wanna to achieve?
IMO just use higher levels, and that just once, not after each Commit/Rollback or before Starting the transaction.

Would it be handy if we map tiNone to the driver defaults (OleDB/ODBC)(in your case tiReadCommited) if not supported?
seems like the MS-ADO backend is doing something similar, otherwise you would'nt run into that trap.

What do you think?

[nlanger]

Yeah you're right, I've changed that now.

Many years ago I developed a large billing software with Delphi5 and Zeos6, on the SQL Server 2008. At that time the database was faster if you used tiNone. That is why I always started and ended the transactions as described (also for SQL-Server2014).

In a small test program, this was also possible with Zeos 7.3. The error described occurs in the large project. This may be a timing problem. I do it this way now and have to say => OleDB is really very fast (2..3 times faster than before :-).

ZConn.Protocol:='OleDB';
ZConn.AutoCommit:=True;
ZConn.Database:='MSOLEDBSQL.1;Persist Security Info=True;User ID=NLsoft;Data Source=NLwin8;Initial Catalog=NL001;MARS Connection=True';
ZConn.TransactIsolationLevel:=tiReadCommitted;
...
ZConn.StartTransaction;

// SQL select / update

ZConn.Commit;
---------------------
Thank you