Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-02 : 15:42:09

James writes "I have 2 stored procedures. One calls and passes data to the other. W/o error checking code it works fine. I'd need to include some error checking code. The procs will be below, w/ only the field and proc names changed. The error code I get directly follows.

Server: Msg 266, Level 16, State 2, Procedure setRequestNote, Line 16
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.
Server: Msg 266, Level 16, State 1, Procedure setRequest, Line 20
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.

CREATE PROCEDURE subProc
@pi bigint,
@sic bigint,
@ri bigint,
@n varchar(50)
as
begin transaction
insert into t2 (pi, sic, ri, n)
values (@pi, @sic, @ri, @n)
if @@error = 0
begin
return (1)
commit transaction
end;
else
begin
return (0)
rollback transaction
end;
GO

CREATE PROCEDURE mainProc
@sic bigint,
@sd varchar(50),
@pci bigint,
@wdti bigint,
@d datetime,
@p varchar(50),
@ld varchar(50),
@n varchar(50),
@d1 datetime,
@d2 datetime,
@d3 bigint
as
begin transaction
insert into requests (pci, wdti, sic, sd, ld, d, p)
values (@pci, @wdti, @sic, @sd, @ld, @d, @p)

execute subProc NULL, @sic, @@identity, @n

if @@error = 0
begin
return (1)
commit transaction
end;
else
begin
return (0)
rollback transaction
end;
GO

thanks in advance for any help you can offer"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-02 : 16:37:32


CREATE PROCEDURE subProc 
@pi bigint, 
@sic bigint, 
@ri bigint, 
@n varchar(50),
@err int
as 
begin transaction 
insert into t2 (pi, sic, ri, n) 
values (@pi, @sic, @ri, @n) 
select @err = @@ERROR

if @err = 0 
begin 
If @@TRANCOUNT>0 commit transaction 
return 0
end 
else 
begin 
If @@TRANCOUNT>0 rollback transaction 
return 1 
end 
GO 

CREATE PROCEDURE mainProc 
@sic bigint, 
@sd varchar(50), 
@pci bigint, 
@wdti bigint, 
@d datetime, 
@p varchar(50), 
@ld varchar(50), 
@n varchar(50), 
@d1 datetime, 
@d2 datetime, 
@d3 bigint,
@ret int,
@err int
 
as 
begin transaction 
insert into requests (pci, wdti, sic, sd, ld, d, p) 
values (@pci, @wdti, @sic, @sd, @ld, @d, @p) 
select @err = @@ERROR

execute @ret = subProc NULL, @sic, @@identity, @n 

if (@err = 0 and @ret = 0)
begin 
If @@TRANCOUNT>0 commit transaction 
return 0
end 
else 
begin 
If @@TRANCOUNT>0 rollback transaction 
return 1 
end 
GO

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources