CDatabase Transactions

[Mike C#]

Hi all,

Have a quick question. I have an app that connects to SQL Server via the
MFC CDatabase class. I'm doing bulk loading using the ODBC bulk opns
extensions for SQL Server. When I try to wrap my bulk inserts in a
transaction using BeginTrans() and CommitTrans(), it doesn't work. When I
use SQLExecDirect() and send a T-SQL "BEGIN TRANSACTION" and "COMMIT
TRANSACTION", it does work.

I was just wondering if there's something peculiar about MFC CDatabase
transaction that keep the BeginTrans() and CommitTrans() from working
correctly with ODBC bulk operations. Also, I was told that I shouldn't use
SQLExecDirect() to execute the T-SQL BEGIN TRANSACTION and COMMIT
TRANSACTION statements. Is this true?

Thanks

[Mike C#]

BTW - it looks to me as if the rationale for not sending T-SQL statements to
begin and commit a transaction is in case your driver doesn't support it. I
can't find any other reason not to... And my driver is guaranteed to
support it, so I'm at a loss for reasons not to?

Thanks

"Mike C#" <xy*@xyz.com> wrote in message
news:uN**************@TK2MSFTNGP02.phx.gbl...

Hi all,

Have a quick question. I have an app that connects to SQL Server via the
MFC CDatabase class. I'm doing bulk loading using the ODBC bulk opns
extensions for SQL Server. When I try to wrap my bulk inserts in a
transaction using BeginTrans() and CommitTrans(), it doesn't work. When I
use SQLExecDirect() and send a T-SQL "BEGIN TRANSACTION" and "COMMIT
TRANSACTION", it does work.

I was just wondering if there's something peculiar about MFC CDatabase
transaction that keep the BeginTrans() and CommitTrans() from working
correctly with ODBC bulk operations. Also, I was told that I shouldn't
use SQLExecDirect() to execute the T-SQL BEGIN TRANSACTION and COMMIT
TRANSACTION statements. Is this true?

Thanks

[Vladimir Nesterovsky]

> Have a quick question. I have an app that connects to SQL Server via the

MFC CDatabase class. I'm doing bulk loading using the ODBC bulk opns
extensions for SQL Server. When I try to wrap my bulk inserts in a
transaction using BeginTrans() and CommitTrans(), it doesn't work. When I
use SQLExecDirect() and send a T-SQL "BEGIN TRANSACTION" and "COMMIT
TRANSACTION", it does work.

I was just wondering if there's something peculiar about MFC CDatabase
transaction that keep the BeginTrans() and CommitTrans() from working
correctly with ODBC bulk operations. Also, I was told that I shouldn't
use SQLExecDirect() to execute the T-SQL BEGIN TRANSACTION and COMMIT
TRANSACTION statements. Is this true?

There are good reasons not to run bulk insert into a context of a
transaction. This can lead to a table and a database locks, and can produce
enormous logs. You may consider creating a SP, which BULK INSERTs into the
temporary tables, and then inserts data into destination tables. A careful
design allows to reconstruct a big transaction into series of small ones.
--
Vladimir Nesterovsky

[Mike C#]

"Vladimir Nesterovsky" <vl******@nesterovsky-bros.com> wrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...

There are good reasons not to run bulk insert into a context of a
transaction. This can lead to a table and a database locks, and can
produce enormous logs. You may consider creating a SP, which BULK INSERTs
into the temporary tables, and then inserts data into destination tables.
A careful design allows to reconstruct a big transaction into series of
small ones.

I'm not using the T-SQL BULK INSERT statement, I'm using the ODBC Bulk
Operations API from C++. I've set it up to use minimal logging (no indexes,
etc.) I've gone down the bulk insert to temp table ---> copy to real table
routine, and the logging is a lot larger + the speed is a lot slower than
just bulk inserting into the table. Basically I can bulk load 50,000 rows
in about 29 seconds, which is about an hour and a half of improvement over
the previous method that was being used to push this data in. The bulk
inserting is occurring late at night, when no one is using the system so
table locking is not an issue. I'm not too concerned about deconstructing
this into a smaller series of transactions. Basically I just need to know
if there is any reason not to use T-SQL BEGIN TRANS and COMMIT TRANS
statements instead of CDatabase methods.

Thanks.