Why does ADO BeginTrans() do something different than "BEGIN TRANSACTION"?

Question

I've run into some surprising behavior while using ADO with C++ and Microsoft SQL Server 2008 (express). Essentially, I had code that did this:

//pseudocode pseudocode pseudocode   
adoConnection->Execute("BEGIN TRANSACTION;");
Insert( adoRecordsetPtr );
SelectAll( adoRecordsetPtr  );
adoConnection->Execute("COMMIT TRANSACTION;");

But when it tried to perform the SelectAll, ADO threw an exception with the following information:

Error: ADO Error -2147217871: 071A14D0
From source: Microsoft OLE DB Provider for SQL Server
Description: Timeout expired

After a bit of sleuthing, I discovered that if I used ado_connection->BeginTrans(), like a sane person would, everything worked as expected. And while this post is mostly here to make the workaround googleable for other people who might encounter it, I also have a question:

Why did this fix the problem?

Here's a little more detail about what's happening with my Insert and SelectAll. Note that SelectAll is using an ADO command object (because in the actual code it isn't doing a select all). The timeout doesn't occur if I use Connection.Execute() instead of Command.Execute().

//Insert
ADODB::_RecordsetPtr prs = NULL;
HRESULT hr = prs.CreateInstance(__uuidof(ADODB::Recordset));
prs->Open(
    table
    _variant_t((IDispatch *) acpAdoConnection),
    ADODB::adOpenUnspecified, 
    ADODB::adLockOptimistic, 
    ADODB::adCmdTable);
prs->AddNew();
//put some stuff into fields using prs->Fields->Item[]
prs->Update();
prs->Close();

//SelectAll
ADODB::_CommandPtr cmd;
cmd.CreateInstance( __uuidof( ADODB::Command ) );
cmd->ActiveConnection = acpAdoConnection;
ADODB::_RecordsetPtr prs2 = NULL;
HRESULT hr2 = prs2.CreateInstance(__uuidof(ADODB::Recordset));
prs2->Open(
    table, 
    _variant_t((IDispatch *) acpAdoConnection),
    ADODB::adOpenUnspecified, 
    ADODB::adLockOptimistic, 
    ADODB::adCmdTable);
std::string sql = "SELECT * FROM [" + table + "] ;";
cmd->CommandText = sql.c_str();
_variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);
//timeout:
ADODB::_RecordsetPtr records = 
    cmd->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );

Answer 1

The short answer is that BEGIN TRANSACTION and cn.BeginTrans() don't behave always the same way. This MSDN article tells you more about this problem:

---

How Does ADO Behave with Respect to Transactions

By default ADO operates in AutoCommit mode, unless you start a implicit transaction by executing Connection.BeginTrans.

Implicit_transactions begin a transaction on the server for each statement, and commits do not occur until they are manually issued.

So,

set implicit_transactions on
go
insert
insert
insert

is internally turned into

BEGIN TRAN
insert
insert
insert
...

The above transaction will not be rolled back or committed unless the user issues the correct statement.

Without implicit transaction, which by default is the behavior of ADO (Auto Commit mode), the following is (conceptually) occurring:

BEGIN TRAN
insert
COMMIT TRAN
BEGIN TRAN
insert
COMMIT TRAN

---

As you can easy see, for your case

BEGIN TRAN
insert
COMMIT TRAN
BEGIN TRAN
select
COMMIT TRAN

is different from:

BEGIN TRAN
insert
select
COMMIT TRAN

... and also maybe not what you are expecting.