How to prevent the DML queries from starting a new transaction in OLEDB?

Question

I am connecting SQL server using a OLE DB driver. I am starting a local transaction using ITransactionLocal interface(ITransactionLocal::StartTransaction with ISOLATIONLEVEL_READCOMMITTED) to begin the transaction. In the middle of transaction, getting an error DB_E_ERRORSINCOMMAND for the Select execution(ICommandText::Execute) and the transaction gets aborted. But, the next following INSERT execution starts a new transaction and gets committed.
But, I dont want it to start a new transaction without explicitly starting a transaction(ITransactionLocal::StartTransaction). How to prevent it?

Answer 1

In SQL Server, by default, each statement is its own transaction which is auto-committed. The alternative is SET IMPLICIT_TRANSACTIONS ON. In this ANSI-compliant mode, each SELECT, INSERT etc start a new transaction which you must commit explicitly.

From what you describe, it seems that you need to improve your error handling, so that you move back to a safe starting point - or simply crash the program on an error. You cannot just continue executing.