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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy