Nesting a stored Procedure inside an ADO transaction (in Access 2007)

n1ckjp
n1ckjp used Ask the Experts™
on
In short...

 Can I wrap an Access 2007 ADO transaction statement around a SQL Server stored procedure (that may contain a transaction itself) and have the various transactions successfully rollback/commit as would be expected. Or does weirdness ensue? (ie implicit auto-commits, etc)

In long...

Having recently gained an understanding of Stored Procedures/Inline Table Valued Functions/etc/etc. I have started making moves to migrate much of my business logic into SQL Server (Express 2008).

With some disgust however I have discovered that passing table-valued parameters from Access 2007 seems to be crippled. (Or rather, just not-implemented) ( http://msdn.microsoft.com/en-us/library/dd721896.aspx ).

My main use for this functionality was to pass a set of user-selected values (say primary keys) through to the stored procedure as a table (along with a couple of other parameters) and having the procedure perfom any inserts/deletes/updates within a server-side transaction. (Returning error-codes, etc)

After some thrashing around I am considering keeping the same logic and just looping through the user-selected keys passing each individually to the stored procedure for processing and then wrapping an ADO transaction around the whole thing.

Is this going to work?

See made-up code below:
(I bashed this out pretty quick so I may have missed a few things in my setup/teardown. Please feel free to point them out since I am still a bit new to ADO)
Sub SomeProcedure(CollOfIds as collection)
 
    On Error GoTo Error_Handler
 
    Dim cnn As ADODB.Connection
    Dim cmd as ADODB.Command
    Dim rst As ADODB.Recordset
    Dim id as variant
 
    Dim boolInTrans As Boolean
 
    boolInTrans = False
 
    Set rst = New ADODB.Recordset
 
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
 
    cnn.ConnectionString = "Some-Connection-String"
 
    With cmd
	.ActiveConnection = con
    	.CommandText = "Stored-Procedure-Name"
    	.CommandType = adCmdStoredProcedure
    	.Parameters.Append .CreateParameter("@ID",adInteger,adParamInput)
	.Parameters.Append .CreateParameter("@ErrCode",adInteger,adParamOutput)
 
	' Possibly other parameters here
 
	cnn.BeginTrans
 
        boolInTrans = True
 
        For Each id in CollofIDs
 
	      .Parameters("@ID") = id
	      .Execute
	      
              ' Probably redundant in this example since Stored Proc code 
              ' should generate an actual error
              ' This is really to demonstrate that I want to return values (perhaps an identity)
	      If .Parameters("@ErrCode") <> 0 Then
		Goto Error_Handler 
	      End If
           	     
        Next
 
    cnn.CommitTrans
 
    boolInTrans = False
 
Exit_Procedure:
    Set cnn = Nothing
    Set rst = Nothing
    Exit Sub
 
Error_Handler:
    msgBox "Error # " & Err.number & ": " & Err.Description
    If boolInTrans Then
        cnn.RollbackTrans
    End If
    Resume Exit_Procedure:
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
teebonTechnology Manager

Commented:
Hi n1ckjp,

The stored procedure should be able to be rollback if there is any error occurs.
Did you received any error message?

Author

Commented:
Thanks for the reply teebon. I don't actually have this arrangement setup yet. I was rather hoping someone would be able to tell me if what I am doing is possible before I go re-writing a pile of code.

Are you saying that, using the example above, if the stored procedure has its own explicit transaction code, the ADO connection will be able to rollback the whole transaction (on error) even if each run of the stored procedure contains a commit?
Technology Manager
Commented:
Hi n1ckip,

In the case of nested transaction in the stored procedure, you are able to rollback the whole transaction from the outer most transaction by defining unique transaction name for your inner transaction.

http://msdn.microsoft.com/en-us/library/ms189336.aspx 
"It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction."

Author

Commented:
Hi Teebon. Well it worked. Thanks for the pointers.

I have attached my unedited code below (please excuse the mess). Also, don't get too caught up in the simplicity of the code, this was really just to test the functionality.

Basically I tested this by stepping through the VBA code and once a few lines had been 'committed' in the SP I then manually forced a Rollback in the VBA.

All assignments were successfully rolled back.
*** SP CODE
 
CREATE PROCEDURE [dbo].[usp_ASCreateAssignment] 
	-- Add the parameters for the stored procedure here
	@ASDate date, 
	@ASEmpID int,
	@ASPItemID int,
	
	@ASID int OUTPUT,
	@ASCount int OUTPUT
AS
BEGIN
 
	DECLARE	@ReturnCode int,
		@ErrorCode int,
		@RowCount int,
		@InTrans tinyint,
		@Message varchar(200)
		-- Initialise Error/Return Codes
	SELECT	@ReturnCode = 0,
		@ErrorCode = 0,
		@RowCount = 0,
		@InTrans = 0,
		@Message = '[usp_ASCreateAssignment]: '
		
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Start Transaction here (Not actually necessary, using to test theory)
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRANSACTION
	SET @InTrans = 1
	
	BEGIN TRY
    -- Insert statements for procedure here
	INSERT INTO dbo.tblAssignment
	(PItemID, EmployeeID, EffectiveDate)
	VALUES
	(@ASPItemID, @ASEmpID, @ASDate)
	
	SET @ASCount = @@ROWCOUNT
	SET @ASID = SCOPE_IDENTITY()
	END TRY
	
	BEGIN CATCH
		IF (ERROR_NUMBER() = 2601) -- Catch Duplicate Errors
		BEGIN
			SET @ASCount = 0
			SET @ASID = 0
			SET @ReturnCode = 101
		END
		ELSE
		
			SET @ReturnCode = 102
			EXECUTE dbo.usp_RethrowError
	END CATCH
 
ProcExit:
	IF @InTrans = 1 BEGIN
		IF @ReturnCode = 0 BEGIN
			COMMIT TRANSACTION
		END ELSE BEGIN
			ROLLBACK TRANSACTION
		END
	END
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	RETURN(@ReturnCode)
END
 
*** VBA CODE
Private Function AssignCheckedSP(intEmpID As Integer, dteAssignDate As Date) As Integer
 
   On Error GoTo Error_Handler
 
   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   
   Dim chkTag As Variant
   Dim intDupCount As Integer
 
   Dim intNewAsgnID As Integer
   
   ' Set duplicate entry counter to 0
   intDupCount = 0
 
   Set cnn = New ADODB.Connection
   With cnn
      .ConnectionString = conmgr.DefaultADOConnString
      .CursorLocation = adUseClient
      .Open
      .Errors.Clear
   End With
 
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = cnn
      .CommandText = "dbo.usp_ASCreateAssignment"
      .CommandType = adCmdStoredProc
      '.Parameters.Refresh
      .Parameters.Append .CreateParameter("@ASDate", adDBTimeStamp, adParamInput)
      .Parameters.Append .CreateParameter("@ASEmpID", adInteger, adParamInput)
      .Parameters.Append .CreateParameter("@ASPItemID", adInteger, adParamInput)
      .Parameters.Append .CreateParameter("@ASID", adInteger, adParamOutput)
      .Parameters.Append .CreateParameter("@ASCount", adInteger, adParamOutput)
   End With
   
   ' Begin DB Transaction
   cnn.BeginTrans
   
   With cmd
      For Each chkTag In objLVHlpr.Checked
         
         ' Set Parameters
         .Parameters("@ASDate") = dteAssignDate
         .Parameters("@ASEmpID") = intEmpID
         .Parameters("@ASPItemID") = CInt(Split(chkTag, "key")(1))   ' Strip out the "key" prefix
         
         ' Execute Command
         .Execute
         
         ' Check Return Values
         If .Parameters("@ASCount") > 0 Then
            intNewAsgnID = .Parameters("@ASID")
         Else
            intDupCount = intDupCount + 1
         End If
 
      Next
 
   End With
   
   cnn.CommitTrans
   
   AssignCheckedSP = intDupCount
   
Exit_Proc:
   'Cleanup here
    
   Set cnn = Nothing
   Set cmd = Nothing
   Exit Function
 
Error_Handler:
   
   DisplayUnexpectedError Err.Number, Err.Description
   
   cnn.RollbackTrans
   AssignCheckedSP = -1
   
   Resume Exit_Proc
   Resume
   
End Function

Open in new window

teebonTechnology Manager

Commented:
Glad that my pointers helped :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial