Nested Rollback/Commits Question

Hello all
I have a question regarding Rollbacks and Committs when
you are nesting stored procedures. My problem is that i
continue to get the following error.
Transaction count after EXECUTE indicates that a COMMIT
or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1
I am not sure what i am doing wrong. Any help would be
appriciated. Here is a sample SP that i may be nesting
in a larger SP.
---
CREATE PROCEDURE usp_Update_Client_Biography
@.Client_id as integer,
@.Biography as varchar(1000)
AS
SET NOCOUNT ON
DECLARE @.intErrorCode integer
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode = 0
BEGIN TRANSACTION
IF @.intErrorCode = 0
BEGIN
UPDATE Client_Biography
SET biography = @.Biography
WHERE biz_association_id = @.Client_id
SELECT @.intErrorCode = @.@.Error
END
IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @.intErrorCode
THANKS
BAPerhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||When you rollback a transaction it will rollback all the transactions that
it is nested in. So you won't have any open transaction by the time you get
to your errorhandler. You can catch this by changing your code (at the end)
to:
IF @.@.TRANCOUNT > 0
IF @.intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
You might want to have a look at the SET XACT_ABORT option. If you have it
on any error will cause the transaction to roll back and prevent any further
code in the batch to execute. The disadvantage of this of course is that you
can't do any custom error handling, the advantage is that you don't have to
do any custom error handling.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||This is a multi-part message in MIME format.
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
declare
@.trancount int
set @.trancount =3D @.@.trancount
if @.trancount =3D 0
begin tran MyTran
else
save tran MyTran
-- do the work, check errors
if @.@.ERROR =3D 0
begin
if @.trancount =3D 0
commit tran
end
else
rollback tran MyTran
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BA Baracus" <JCoxEUP@.hotmail.com> wrote in message =news:06ea01c3810f$02b36670$a001280a@.phx.gbl...
Thanks for the help!
Is there any way you can show me some T-SQL that will show me your suggestion? If i understand you correctly, i should check the transaction count before beginning the transaction? This has been an ongoing issue with me and i am very appriciative of your help!
thanks BA
>--Original Message--
>Perhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
>trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=3Ddjq&as=20
ugroup=3Dmicrosoft.public.sqlserver
>
>"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
>> Hello all
>> I have a question regarding Rollbacks and Committs when
>> you are nesting stored procedures. My problem is that i
>> continue to get the following error.
>> Transaction count after EXECUTE indicates that a COMMIT
>> or ROLLBACK TRANSACTION statement is missing. Previous
>> count =3D 0, current count =3D 1
>> I am not sure what i am doing wrong. Any help would be
>> appriciated. Here is a sample SP that i may be nesting
>> in a larger SP.
>> ---
>> CREATE PROCEDURE usp_Update_Client_Biography
>> @.Client_id as integer,
>> @.Biography as varchar(1000)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.intErrorCode integer
>> SELECT @.intErrorCode =3D @.@.Error
>> IF @.intErrorCode =3D 0
>> BEGIN TRANSACTION
>> IF @.intErrorCode =3D 0
>> BEGIN
>> UPDATE Client_Biography
>> SET biography =3D @.Biography
>> WHERE biz_association_id =3D @.Client_id
>> SELECT @.intErrorCode =3D @.@.Error
>> END
>>
>> IF @.intErrorCode =3D 0 AND @.@.TRANCOUNT > 0
>> COMMIT TRANSACTION
>> ELSE
>> ROLLBACK TRANSACTION
>>
>> RETURN @.intErrorCode
>>
>> THANKS
>> BA
>
>.
>
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
declare
@.trancount int
set @.trancount =3D =@.@.trancount
if @.trancount =3D =0
begin tran MyTran
else
save tran MyTran
-- do the work, check =errors
if @.@.ERROR =3D 0
begin
if =@.trancount =3D 0
= commit tran
end
else
rollback =tran MyTran
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"BA Baracus" wrote in =message news:06ea01c3810f$02=b36670$a001280a@.phx.gbl...Thanks for the help!Is there any way you can show me some T-SQL that =will show me your suggestion? If i understand you correctly, i =should check the transaction count before beginning the transaction? =This has been an ongoing issue with me and i am very appriciative of your help!thanks BA >--Original Message-->Perhaps @.@.TRANCOUNT is 1 when you enter the =procedure? If you exit the proc with some other>trancount then when =entering, you get such error. And remember that ROLLBACK exits the transaction>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.>>-- >Tibor Karaszi, SQL Server MVP>Archive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver>>>"B.A. =Baracus" =wrote in message news:065201c38109$f1=f0e150$a001280a@.phx.gbl...> Hello all>> I have a question regarding Rollbacks =and Committs when> you are nesting stored procedures. My =problem is that i> continue to get the following error.>> Transaction count after EXECUTE indicates =that a COMMIT> or ROLLBACK TRANSACTION statement is missing. Previous> count =3D 0, current count =3D =1>> I am not sure what i am doing wrong. Any help would be> appriciated. Here is a sample SP that i may be nesting> =in a larger SP.>> --->>= CREATE PROCEDURE usp_Update_Client_Biography> @.Client_id as integer,> @.Biography as varchar(1000)>> =AS>> SET NOCOUNT ON>> =DECLARE @.intErrorCode integer> SELECT @.intErrorCode =3D @.@.Error>> IF @.intErrorCode =3D 0> BEGIN =TRANSACTION>> IF @.intErrorCode =3D 0> BEGIN> UPDATE Client_Biography> SET biography =3D @.Biography> WHERE biz_association_id =3D @.Client_id>> SELECT @.intErrorCode =3D =@.@.Error> END>>> IF @.intErrorCode =3D 0 AND =@.@.TRANCOUNT > 0> COMMIT TRANSACTION> ELSE> =ROLLBACK TRANSACTION>>> RETURN @.intErrorCode>>> THANKS> BA>>>.>

---

--=_NextPart_000_009C_01C380EE.56F75F50--|||Hi.
I'm currently reading SQL Server 2000 Stored Proc 7 XML Programing,
2nd ed. By Dejan Sunderic - ISBN 0-07-222896-2
So far it has been an excellent book IMHO though I'm only on chapter 9
I have just read the chapter dealing with errorcodes & transactions,
using the information in the book I've got a template that I've
started to use for all my stored procs which might be of some use to
you.
What I would like is if any Guru out there can comment on the template
and let me know before I go to far with it if its as good as what the
book seems to be saying it is.
Thanks & HTH.
Al
/*
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Function : usp__Template
** Language : T-SQL
** Description : This is a template for all stored procs
** :
** Input : None
** Returns : None
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Ver Date Description of modification
**
---
** 1.0 date here text here
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
*/
CREATE PROCEDURE [dbo].[usp__Template]
-- @.intInValuesHere INT ,
-- @.intInValuesMoreHere INT ,
-- @.intOutputParam INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.intErrorCode INT , -- to store the errorcodes
@.intTranCountOnEntry INT , -- Store transaction count
@.intAnotherVariable INT
SELECT @.intErrorCode = @.@.Error ,
@.intTranCountOnEntry = @.@.TranCount ,
@.intAnotherVariable = 0
if @.@.TranCount = 0
BEGIN TRANSACTION
-- T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
-- more T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
IF @.@.TranCount > @.intTranCountOnEntry
COMMIT TRANSACTION
RETURN 0
/****************************************
******* ERROR HANDLER CODE BELOW ********
*****************************************/
ERROR_HANDLER:
IF @.@.TranCount > @.intTranCountOnEntry
ROLLBACK TRANSACTION
RAISERROR ('Some Error Message here...',16,1)
RETURN @.intErrorCode
GO
On Mon, 22 Sep 2003 06:03:41 -0700, "B.A. Baracus"
<JCoxEUP@.hotmail.com> wrote:
>Hello all
>I have a question regarding Rollbacks and Committs when
>you are nesting stored procedures. My problem is that i
>continue to get the following error.
>Transaction count after EXECUTE indicates that a COMMIT
>or ROLLBACK TRANSACTION statement is missing. Previous
>count = 0, current count = 1
>I am not sure what i am doing wrong. Any help would be
>appriciated. Here is a sample SP that i may be nesting
>in a larger SP.
>---
>CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
>AS
>SET NOCOUNT ON
>DECLARE @.intErrorCode integer
>SELECT @.intErrorCode = @.@.Error
>IF @.intErrorCode = 0
> BEGIN TRANSACTION
>IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
>IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
>ELSE
> ROLLBACK TRANSACTION
>
>RETURN @.intErrorCode
>
>THANKS
>BA