question

ParvathyPriyaA-6760 avatar image
0 Votes"
ParvathyPriyaA-6760 asked MelissaMa-msft edited

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.