SQL-Server OleDB TransactIsolationLevel Read../None
Posted: 14.10.2020, 22:38
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?
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?