Rollback statement missing

Question

Post reply

Rollback statement missing

Richard Banks

SSC Journeyman

Points: 80
More actions
July 1, 2010 at 2:28 am

#387256

Hi Guys. Can someone tell me why i get a "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing" when i run the blow stored procedure.
BEGIN
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM FeeAllocations
IF @@Error <> 0 BEGIN
ROLLBACK TRANSACTION
END ELSE BEGIN
INSERT INTO FeeAllocations
SELECT
FeeAllocationID,
FeeLetterID,
JobSuffixID,
Notes,
Amount,
NonRecExpenses,
NonRecOutsideConsultants,
EmployeeID,
DateSaved
FROM
someserver.FeeAllocations
IF @@Error <> 0 BEGIN
ROLLBACK TRANSACTION
END ELSE BEGIN
COMMIT TRANSACTION
END
END
END
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Dave Ballantyne

SSC-Dedicated

Points: 33667

July 1, 2010 at 2:30 am

#1187508

Is there a question here ?

Clear Sky SQL
My Blog[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Have you checked to make sure that the COMMIT/ROLLBACK in your stored proc is not affecting an existing transaction?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Did you check the flow?

What's gonna happen if the first statement returns an error?

Your code will rollback transction and continue - then what?

This way it should work:

SET NOCOUNT ON;

DECLARE @ErrNo int

BEGIN TRANSACTION

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

DELETE FROM FeeAllocations

SELECT @ErrNo = @@Error

IF @ErrNo <> 0 BEGIN

GOTO Finish

END

INSERT INTO FeeAllocations

SELECT

FeeAllocationID, FeeLetterID, JobSuffixID, Notes,

Amount, NonRecExpenses, NonRecOutsideConsultants, EmployeeID, DateSaved

FROM someserver.FeeAllocations

IF @ErrNo <> 0 BEGIN

GOTO Finish

END

Finish:

IF @ErrNo <> 0

ROLLBACK TRANSACTION

ELSE

COMMIT TRANSACTION

END

_____________
Code for TallyGenerator

Richard Banks SSC Journeyman Points: 80 More actions · Answer 4

yes it will continue but it want execute the code in the second if statement. Is this not a good way of doing it?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Richard Banks (7/2/2010)
yes it will continue but it want execute the code in the second if statement. Is this not a good way of doing it?

No, that is fine.

As I said previously, it seems like you are affecting an existing transaction....

If you commit or rollback within a stored proc and the calling code already has an existing transaction you will get an error.

Try this:

create proc test_tran @commit_or_rollback char(1)

as

if @commit_or_rollback='R'

-- a simple rollback

rollback tran

else

-- a simple commit

commit tran

go

begin tran-- start a transaction

exec test_tran'R'

commit tran-- try to commit - fails

go

begin tran-- start a transaction

exec test_tran'C'

commit tran-- try to commit - fails

go

drop proc test_tran

go

Now try this, it works

create proc test_tran @commit_or_rollback char(1)

as

declare @tran_count int

set @tran_count = @@TRANCOUNT

if @tran_count=0

begin tran

else

save tran "test_tran"

-- do some stuff

if @tran_count=0

if @commit_or_rollback='R'

rollback tran

else

commit tran

else

if @commit_or_rollback='R'

rollback tran "test_tran"

--elseThere is no need to do anything here as the external transaction can control whether we commit or not

--commit tran

go

begin tran

exec test_tran 'R'

commit tran

go

begin tran

exec test_tran 'C'

commit tran

go

drop proc test_tran

go

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Richard Banks SSC Journeyman Points: 80 More actions · Answer 6

thanks for that. ill have a look. On another note. How does sql server handle syntax errors with transactions. Would the transaction still be rolled back or would the stored proc stop execution immediatley thus causing the rollback statement problem?