 |
09-09-2005, 11:29 PM
|
 |
Junior Contributor
|
|
Join Date: Jun 2005
Location: Sharjah, UAE
Posts: 299
|
|
Auto commit transaction?
|
Hi! Just one quick question. Is ADO set to auto commit transactions? Let's say in my VB application...
Code:
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? |
__________________
Pinoy ako, pinoy tayo! Ipagmalaki mo...
In the keyboard of life, always put your fingers ready for the ESC button...
|
09-10-2005, 12:28 AM
|
 |
fully realized avatar
Super Moderator
* Expert *
|
|
Join Date: Jun 2004
Location: Davao Philippines
Posts: 2,295
|
|
|
09-10-2005, 01:38 AM
|
 |
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,386
|
|
Quote:
|
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; Chapter 7. |
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
|
09-10-2005, 05:42 AM
|
|
Junior Contributor
|
|
Join Date: Jun 2005
Location: Sharjah, UAE
Posts: 299
|
|
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...
Code:
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? |
__________________
Pinoy ako, pinoy tayo! Ipagmalaki mo...
In the keyboard of life, always put your fingers ready for the ESC button...
|
09-11-2005, 09:34 PM
|
|
Junior Contributor
|
|
Join Date: Jun 2005
Location: Sharjah, UAE
Posts: 299
|
|
Quote:
|
Originally Posted by trojanz
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...
Code:
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? |
 |
__________________
Pinoy ako, pinoy tayo! Ipagmalaki mo...
In the keyboard of life, always put your fingers ready for the ESC button...
|
09-12-2005, 12:18 AM
|
|
fully realized avatar
Super Moderator
* Expert *
|
|
Join Date: Jun 2004
Location: Davao Philippines
Posts: 2,295
|
|
|
09-12-2005, 10:53 AM
|
|
Google Hound
Retired Moderator
* Guru *
|
|
Join Date: Nov 2001
Location: Arizona, USA
Posts: 12,386
|
|
|
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.
|
__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown
Last edited by loquin; 09-12-2005 at 01:21 PM.
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
