Can't execute SQL batch in ONE transaction.

[hawran]

I've got two classes(two components in one DLL): clsA a clsB. Both clsA and clsB "Require transaction". The class clsA has got the only one public function which executes SQL command, passed to this function as String parameter. Another parameter passes an opened DB connection object. Before the "Exit Function" statement is called EnableCommit() function, in the error treatment is called SetAbort() function and Err.Raise xxx. Being called for one SQL command, class clsA acts well. A SQL command is executed and an error causes "rollback transaction".
The class clsB was designed for executing of SQL batches, however always in ONE trasaction. The clsB has got the only one public function too. The SQL batch is passed to this function as String array. This function creates an object of clsA(!) type and creates and opens a DB connection object. The above descibed function is called inside a loop. This loop passes the connection object and the individual elements of the string array one by one into called function. Before the "Exit Function" statement is called SetComplete() function, in the error treatment is called SetAbort() function and Err.Raise xxx.
And that DOESN'T WORK!!! Whatever error inside SQL batch ends the loop but SQL commands executed before ARE NOT roll-backed! What' s wrong?

Thanks a lot!
(Win 2k + SQL Server 2000)

 

[hawran]

Hawran - addendum.
I've found out that any potential error in an SQL batch, which is executed inside ONE component (in a loop), WILL NOT cause roll-back of any SQL command executed before! Is it normal?

 

[eirikur]

Why don't you just execute the batch as a whole?

Public Sub ExecuteBatch(ByVal vntStatementArray As Variant)

'* Enable error handling
On Error GoTo errHandler

'* Declare variables
Dim strSQL As String
Dim objConn As New ADODB.Connection

'* Join the array into a single batch string
strSQL = Join(vntStatementArray, ";&quot

'* Open connection
objConn.Open "ConnectionStringHere"

'* Execute batch
objConn.BeginTrans '* This might not be necessary, I think COM+ does this automatically
objConn.Execute strSQL
objConn.CommitTrans '* This might not be necessary, I think COM+ does this automatically

'* Clean up
objConn.Close
Set objConn = Nothing

'* Then call SetComplete or EnableCommit (on the object context if I remember correctly)

errHandler:

'* Call SetAbort (on the object context if I remember correctly)
Err.Raise vbObjectError + 1, "XXX"
End Sub