ADO Begin/Commit Transactions 
Author Message
 ADO Begin/Commit Transactions

I have a VB program that is loading hundreds of thousands of records into a
table from another source.  After several thousand records the program blows
up because the log file is full.

So what I thought was all I have to do is place a BeginTrans and CommitTrans
in and set it to fire the committrans every 50000 records and everything
would be fine.  Ha,  The program always blows up after the first commit on
the next addNew statement.  I have tried everything I can think of.  Could
someone offer some advice on how to do this?

I am using VB6, ADO 2.5. OLE DB and SQL7.

Thanks,

Don Norris
Sirron Software Corporation



Tue, 24 Dec 2002 03:00:00 GMT  
 ADO Begin/Commit Transactions

My very best, and very friendliest, advice to you is to completely s{*filter*}the
method you are attempting.  I can't tell you the nightmares I am going to
have for the next couple weeks thinking about your app!

Best solution is to use BCP and turn on 'Select into Bulk Copy'.  This
option turns off logging when you are bulk copying large numbers of records
into the database.

BCP is a command line utility that is designed specifically for doing what
you are attempting.  I can't say that it is easy to figure out, or any
easier to make it work right once you do figure it out, but it is still the
cleanest, fastest way of doing what you are trying.

Kirk Graves
MCSD, MCT

Quote:

>I have a VB program that is loading hundreds of thousands of records into a
>table from another source.  After several thousand records the program
blows
>up because the log file is full.

>So what I thought was all I have to do is place a BeginTrans and
CommitTrans
>in and set it to fire the committrans every 50000 records and everything
>would be fine.  Ha,  The program always blows up after the first commit on
>the next addNew statement.  I have tried everything I can think of.  Could
>someone offer some advice on how to do this?

>I am using VB6, ADO 2.5. OLE DB and SQL7.

>Thanks,

>Don Norris
>Sirron Software Corporation



Sat, 28 Dec 2002 03:00:00 GMT  
 ADO Begin/Commit Transactions

Don,
        I must say I agree with Kirk. You should look into using SQL-DMO. Here is
an article that may help:

http://support.microsoft.com/support/kb/articles/q152/8/01.asp

Thanks,
Terrell A.
Microsoft Developer Support



Sun, 29 Dec 2002 03:00:00 GMT  
 ADO Begin/Commit Transactions

Did you try INSERT instead of ADDNEW?


Quote:
> I have a VB program that is loading hundreds of thousands of records into
a
> table from another source.  After several thousand records the program
blows
> up because the log file is full.

> So what I thought was all I have to do is place a BeginTrans and
CommitTrans
> in and set it to fire the committrans every 50000 records and everything
> would be fine.  Ha,  The program always blows up after the first commit on
> the next addNew statement.  I have tried everything I can think of.  Could
> someone offer some advice on how to do this?

> I am using VB6, ADO 2.5. OLE DB and SQL7.

> Thanks,

> Don Norris
> Sirron Software Corporation



Sun, 29 Dec 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Begin-Rollback-Commit Transaction

2. Workspace Transaction Begin/Commit/Rollback bug?

3. Limits on Transactions between Begin & Commit

4. ADO and Transaction in SQL Server 7.0: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

5. ADO err with trans begin/commit ?

6. Access97 ADO SQL7 transactions - undefined recordset after COMMIT

7. Stored Procedure and ADO.net Begin Transaction Code

8. Error beginning first transaction with Vb+Ado+Sql7

9. Error beginning first transaction with Vb+Ado+Sql7

10. begin trans - commit trans

11. BEGIN,COMMIT Trans

12. VB6 - DAO Begin/Commit & Rollback

 

 
Powered by phpBB® Forum Software