January 28, 2010 at 5:59 am
I am getting the following error message w.r.t. transactions.
Msg 266, Level 16, State 2, Procedure mytmp, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Below is the sample stored procedure, table that I created for testing purpose. Getting the above specified error. Can anyone tell me the logic to be followed w.r.t. handling transactions?
create procedure mytmp (@stats int output)
as
begin
insert into x values (1)
set @stats = @@error
if @stats <> 0
begin
rollback tran
return @stats
end
insert into x values ('t')
set @stats = @@error
if @stats <> 0
begin
rollback tran
return @stats
end
insert into x values (3)
set @stats = @@error
if @stat <> 0
begin
rollback tran
return @stats
end
end
create table x (y int)
January 28, 2010 at 6:05 am
Your are using rollback transactions without commit statement.
You need to use begin tran,commit and rollback in the block.
January 28, 2010 at 6:41 am
Where is the BEGIN TRANSACTION?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply