Auto commit transaction?

Hi! Just one quick question. Is ADO set to auto commit transactions? Let's say in my VB application...


cmdSQL = "INSERT INTO Invoices (JPO, Customer, InvNo, InvSQM, InvValue, InvDate) " & _
"SELECT a.AUF_NR AS OrdNo, k.KU_NAME AS Customer, b.RG_NR AS InvNo, SUM(b.RG_ANZ * c.VER_M2) AS Qty, " & _
"SUM(b.RG_ANZ * c.SUM_NETTO) AS InvValue, b.RG_DAT AS InvDate " & _
"FROM liorder..LIORDER.AUF_STAT b INNER JOIN " & _
"liorder..LIORDER.AUF_KOPF a ON b.AUF_NR = a.AUF_NR INNER JOIN " & _
"liorder..LIORDER.AUF_POS c ON b.AUF_NR = c.AUF_NR AND b.AUF_POS = c.AUF_POS INNER JOIN " & _
"liorder..LIORDER.KUST_ADR k ON a.KUNR = k.KU_NR " & _
"WHERE (a.AUF_OFF = 0) And (k.KU_VK_EK = 0) And (k.KU_ADR_ART = 0) And b.RG_DAT >= CONVERT(CHAR(10), GETDATE(), 101) " & _
"GROUP BY a.AUF_NR, b.RG_NR, a.RG_NUM, b.RG_DAT, k.KU_NR, k.KU_NAME, a.KOPF_ABSCHL"
DB.CommandTimeout = 0
DB.Execute cmdSQL


...it will automatically commit this transaction and not stay in the transaction log file in SQL Server if successful and rollback if not? And by issuing such commands, it automatically begins a transaction?

Hi,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp
:)

ADO allows you to begin and commit individual transactions explicitly (if transactions are supported by the provider). If you don't explicitly define a transaction, ADO runs in auto-commit mode, meaning each change to the database is treated as a single transaction. For instance, updating records using a SQL statement is considered a single transaction, as is deleting a record using the Delete method of the Recordset object.
The above is a quote from O'Reilly's MCSD in a Nutshell (http://http://www.oreilly.com/catalog/mcsdnut/chapter/ch07.html); Chapter 7.

If that's the case, then why is the log file growing and the size remains the same if it's automatically committing all transactions? I noticed this though when performing bulk transactions. For once I suspected that my VB application is not committing all transactions. But after a series of testing, I noticed that when it performs insert transaction of let's say, not more than 100 rows, and the log file is 4.56MB (restricted to 5 MB), it runs well. But after perfoming a huge insert coming from an SQL command such as this...


INSERT INTO InvoicesEntered (JPO, CustNo, Pos, InvNo, InvDate, Disc, CalcMode, Total, Gl1, Sp1, Gl2, Sp2, Gl3, CostCtr)

SELECT x.auf_nr as OrdNo, z.kunr as CustNo, x.auf_pos as Pos,x.rg_nr as InvNo,x.rg_dat as InvDate, z.kopf_abschl as Disc, y.ver_art as CalcMode, sum(y.sum_netto*x.rg_anz) as TotSQM, y.glas1 as Gl1, y.lzr1 as Sp1, y.glas2 as Gl2, y.lzr2 as Sp2, y.glas3 as Gl3, y.pos_kostenst as CostCtr

FROM liorder..LIORDER.AUF_STAT x, liorder..LIORDER.AUF_POS y, liorder..LIORDER.AUF_KOPF z

WHERE z.auf_off = 0 and z.auf_nr = x.auf_nr and z.auf_nr = y.auf_nr and x.auf_pos = y.auf_pos and x.rg_dat >= DATEADD(DD,-7,CONVERT(CHAR(10),GETDATE(),101))

group by x.auf_nr, x.auf_pos, x.rg_nr, x.rg_dat, y.glas1, y.glas2, y.glas3, z.kunr,z.kopf_abschl, y.lzr1,y. lzr2, y.ver_art, y.pos_kostenst

Order by x.rg_nr


...it returns an error saying that the log is full. Is there any solution for this?

If that's the case, then why is the log file growing and the size remains the same if it's automatically committing all transactions? I noticed this though when performing bulk transactions. For once I suspected that my VB application is not committing all transactions. But after a series of testing, I noticed that when it performs insert transaction of let's say, not more than 100 rows, and the log file is 4.56MB (restricted to 5 MB), it runs well. But after perfoming a huge insert coming from an SQL command such as this...


INSERT INTO InvoicesEntered (JPO, CustNo, Pos, InvNo, InvDate, Disc, CalcMode, Total, Gl1, Sp1, Gl2, Sp2, Gl3, CostCtr)

SELECT x.auf_nr as OrdNo, z.kunr as CustNo, x.auf_pos as Pos,x.rg_nr as InvNo,x.rg_dat as InvDate, z.kopf_abschl as Disc, y.ver_art as CalcMode, sum(y.sum_netto*x.rg_anz) as TotSQM, y.glas1 as Gl1, y.lzr1 as Sp1, y.glas2 as Gl2, y.lzr2 as Sp2, y.glas3 as Gl3, y.pos_kostenst as CostCtr

FROM liorder..LIORDER.AUF_STAT x, liorder..LIORDER.AUF_POS y, liorder..LIORDER.AUF_KOPF z

WHERE z.auf_off = 0 and z.auf_nr = x.auf_nr and z.auf_nr = y.auf_nr and x.auf_pos = y.auf_pos and x.rg_dat >= DATEADD(DD,-7,CONVERT(CHAR(10),GETDATE(),101))

group by x.auf_nr, x.auf_pos, x.rg_nr, x.rg_dat, y.glas1, y.glas2, y.glas3, z.kunr,z.kopf_abschl, y.lzr1,y. lzr2, y.ver_art, y.pos_kostenst

Order by x.rg_nr


...it returns an error saying that the log is full. Is there any solution for this?

:mad:

Hello,
- you might find some insights here.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_7vaf.asp

The log file isn't just used for transaction control. It also enables you to recreate all the changes in your database since the last database backup. Because of this, it doesn't get emptied (truncated) until you make it so.

If you are using the simple recovery model, all you need to do is to periodically backup the database, which implicitly truncates the log file.

However, if you're not backing up your database, (or, if you not using the simple recovery model, and you're not truncating your log file) the log continues to grow. So, as a part of the backup routine, truncate the transaction log.

You can easily set up an automatic backup of the database, so that you won't have this problem in the future.