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

Parvathy Priya A 1 Reputation point
2021-10-06T11:19:26.137+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,334 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 106.6K Reputation points
    2021-10-06T21:52:09.203+00:00

    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.

    0 comments No comments