March 7, 2006 at 9:30 am
I have some nested stored procedures where one sp calls another, etc. I need this wrapped in a transaction so that if an error occurs on any one sp (either the calling sp or the one that is called) it will fail.
I'm continualy getting this error: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3." So I've been playing around with where to put the Begin Tran, Committ,Rollback, etc. not only in the calling proc (spFILE_PROCESS), but within the other sps as well.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
---------------------------------------------------
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer and spInsert_Trans to be their own transaction as I call these sps by themselves
elsewhere in my application.
March 7, 2006 at 10:42 am
A single stored procedure call has an implied transaction.
If your calling multiple then you simply have to create a begin tran, commit tran in the wrapper. Not in the individual stored procedures.
Create proc myproc @param int
as
Declare @error int
Begin Tran
Exec myproc1 @param
set @error = @@error
if @error = 0
Begin
Exec Myproc2 @Param
Set @error = @@Error
End
If @error <> 0
Rollback tran
Else
Commit Tran
GO
March 7, 2006 at 2:01 pm
That makes sense, but from my first post, I specified that I need the called sps to also be their own transactions.
March 8, 2006 at 10:40 am
Ray has the answer for handling the transactions in the procedure.
Check out the SQL BOL topic for 'nested transactions' for a detailed explanation. The following are quotes from it:
"Committing inner transactions is ignored by Microsoft® SQL Server™. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed."
and
"The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all the nested transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction."
March 8, 2006 at 11:01 am
Okay, so I set up my wrapper like Ray suggested. Then I forced an error in spInsert_Customer by trying to insert a string of text into a DateTime field. However, the Transaction was not rolled back. This is what it looks like:
CREATE PROCEDURE [dbo].[spBATCH_FILE_PROCESS]
AS
BEGIN TRAN
---Run some queries, etc, and then:
EXEC sp_addNewCustomerAndAccount
IF @@ERROR <> 0 BEGIN -- AND @@TRANCOUNT = 0
PRINT 'ROLLBACK OF spBATCH_FILE'
ROLLBACK TRAN
RETURN
END
IF @@ERROR = 0 BEGIN
PRINT 'HERE'
COMMIT TRAN
END
Now what? In sp_addNewCustomerAndAccount, I have also added 'Return @@Error' but with no success.
March 8, 2006 at 11:40 am
Edited
March 8, 2006 at 11:49 am
The following example causes a rollback:
drop procedure sp_addNewCustomerAndAccount
go
Create procedure sp_addNewCustomerAndAccount
as
RAISERROR ('ERROR', 10, 1)
go
DROP PROCEDURE [dbo].[spBATCH_FILE_PROCESS]
go
CREATE PROCEDURE [dbo].[spBATCH_FILE_PROCESS]
AS
declare @Err int
BEGIN TRAN
---Run some queries, etc, and then:
EXEC sp_addNewCustomerAndAccount
select @Err = @@Error
IF @Err <> 0 BEGIN -- AND @@TRANCOUNT = 0
PRINT 'ROLLBACK OF spBATCH_FILE'
ROLLBACK TRAN
RETURN
END
IF @Err = 0 BEGIN
PRINT 'HERE'
COMMIT TRAN
END
go
exec spBATCH_FILE_PROCESS
March 8, 2006 at 11:54 am
Another example of how to trap the error:
drop table customer
go
create table customer(adddate datetime NOT NULL)
go
drop procedure sp_addNewCustomerAndAccount
go
Create procedure sp_addNewCustomerAndAccount
as
insert Customer Values(NULL)
RETURN @@ERROR
go
DROP PROCEDURE [dbo].[spBATCH_FILE_PROCESS]
go
CREATE PROCEDURE [dbo].[spBATCH_FILE_PROCESS]
AS
declare @Err int
BEGIN TRAN
---Run some queries, etc, and then:
EXEC @Err = sp_addNewCustomerAndAccount
--select @Err = @@Error
IF @Err <> 0 BEGIN -- AND @@TRANCOUNT = 0
PRINT 'ROLLBACK OF spBATCH_FILE'
ROLLBACK TRAN
RETURN
END
ELSE IF @Err = 0 BEGIN
PRINT 'HERE'
COMMIT TRAN
END
go
exec spBATCH_FILE_PROCESS
March 8, 2006 at 2:22 pm
Jeff:
I set up my sp to mimick your example and it still is not rolling back the transaction. Here is the output in QA:
Syntax error converting datetime from character string.
0
HERE
0
-------------------------------------------------
Is this not a fatal error? I had added 'Print @Err' just below 'EXEC @Err = sp_addNewCustomerAndAccount. ' Appreciate your help.
March 8, 2006 at 2:29 pm
The reason that it is failing is that some errors cause the batch to terminate, instead of the statement. In this case, each EXEC is considered a batch. Here is a link describing what errors cause what types of terminations:
http://www.sommarskog.se/error-handling-I.html#statementbatch
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply