October 1, 2002 at 2:40 am
I have a conceptual problem with stored procedure and transaction. To summarize, I have stored procedures which call another stored procedures.
Example: “SP_A” call “SP_B”. Both stored procedure can be call by the application. The problem is that each sp has his own transaction. If I call “SP_B” every thing it’s ok. “SP_B” makes a commit or a rollback properly if I have an error.
Now when I run “SP_A”, if I have no error, it’s ok.
But if I have an error inside “SP_B”, when it “SP_A” which call it, when it try to rollback the transaction of SP_B, it says that : “Server: Msg 6401, Level 16, State 1, Procedure TEST1, Line 10
Cannot roll back TOTO. No transaction or savepoint of that name was found.
Server: Msg 266, Level 16, State 2, Procedure TEST1, Line 11
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.”
You could find the code below of the stored procedure. What do you think about this way of programming? In fact, I’m trying to not create 2 kind of stored procedure, one usable directly by application with transaction and one other useable by another stored procedure without transaction. Do you have any solution for me? I’m really open to see how you do it.
Regards.
Jmackels
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST1]
GO
CREATE PROCEDURE TEST1
@ERROR INT OUTPUT
AS
BEGIN TRAN TOTO
SET @ERROR = 123
PRINT 'TRAN COUNT'
PRINT @@TRANCOUNT
ROLLBACK TRAN TOTO
RETURN(-1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST2]
GO
CREATE PROCEDURE TEST2
@ERROR INT OUTPUT
AS
PRINT 'BEFORE BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
BEGIN TRAN COCO
PRINT 'AFTER BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
EXEC TEST1 @ERROR OUTPUT
IF @ERROR <> 0
BEGIN
Print 'Erreur dans sous requete'
ROLLBACK TRAN COCO
RETURN(-1)
END
SET @ERROR = 0
RETURN(0)
GO
-- Test Script
DECLARE @ERROR INT
EXEC TEST2 @ERROR OUTPUT
PRINT @ERROR
October 1, 2002 at 8:30 am
This is straight from books online, but I think this explains your issue:
Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 2, 2002 at 2:45 am
The little tricks that I’ve found yesterday, is use the @@TRANCOUNT.
Make a rollback only if it’s equal to 1. It means that you are on the outer level and you can make a rollback.
If you are on a higher level than 1, it means that you are encapsulated by another transaction. You make a commit. It’s really crazy because only the commit decrease the @@TRANCOUNT. But if you do that, each time you run a stored procedure, you must check the return value or the error output parameters and if it’s an error you rollback the outer transaction.
If I well understand rollback put the @@TRANCOUNT to 0 even if it’s equal to 2 or 3. And when you quit the stored procedure, the system check if the @@TRANCOUNT is the same when you enter it.
You can see an example of the code below
Do you think that it'll be possible to create a savepoint directly after I create the transaction and if I have an error I make a rollback to the savepoint and every time I leave the stored procedure, I make a commit? If it allows in the innertransaction, it will solve my problem.
-- -----------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST1]
GO
CREATE PROCEDURE TEST1
@ERROR INT OUTPUT
AS
BEGIN TRAN TOTO
PRINT 'TRAN COUNT'
PRINT @@TRANCOUNT
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRAN TOTO
SET @ERROR = 123
END
ELSE
BEGIN
COMMIT TRAN TOTO
SET @ERROR = 456
END
RETURN(-1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST2]
GO
CREATE PROCEDURE TEST2
@ERROR INT OUTPUT
AS
PRINT 'BEFORE BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
BEGIN TRAN COCO
PRINT 'AFTER BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
EXEC TEST1 @ERROR OUTPUT
IF @ERROR <> 0
BEGIN
Print 'Erreur dans sous requete'
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRAN COCO
SET @ERROR = 123
END
ELSE
BEGIN
COMMIT TRAN COCO
SET @ERROR = 456
END
RETURN(-1)
END
SET @ERROR = 0
RETURN(0)
GO
-- Test Script
DECLARE @ERROR INT
EXEC TEST2 @ERROR OUTPUT
PRINT @ERROR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply