Re: TRANSACTION ERROR
From: oj (nospam_ojngo_at_home.com)
Date: 02/18/04
- Next message: oj: "Re: problem with OSQL in a batch file"
- Previous message: Prabhat: "Re: Need Help in QUERY"
- In reply to: harsh: "TRANSACTION ERROR"
- Next in thread: harsh: "Re: TRANSACTION ERROR"
- Reply: harsh: "Re: TRANSACTION ERROR"
- Reply: harsh: "Re: TRANSACTION ERROR"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 17 Feb 2004 23:47:09 -0800
I'm unable to reproduce this. The only error I get back is
"Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'not_existing_table'."
and the batch is terminated but the commit/rollback tran is never called. The
transaction(s) will stay open for the duration of the connection or until you
explicitly issue a commit/rollback. #1 is based on your post. #2 is how you
would do to *trap* the error and rollback accordingly.
e.g.
--#1
create table t(i int)
go
declare @error int
begin transaction test
insert t values(1)
set @Error=@@error
if @error=0
begin
update t set i=2
set @error=@@error
end
select * from not_existing_table
if @error =0
begin
commit transaction test
end
else
begin
rollback transaction test
end
go
select trancnt=@@trancount
select * from t
go
drop table t
go
-- --#2
-- create table t(i int)
-- go
-- declare @error int
--
-- begin transaction test
-- insert t values(99)
--
-- set @Error=@@error
-- if @error=0
-- begin
-- update t set i=@@trancount
-- set @error=@@error
-- end
--
-- exec('select * from not_existing_table')
--
-- set @error=@@error
-- if @error =0
-- begin
-- commit transaction test
-- end
-- else
-- begin
-- rollback transaction test
-- end
--
-- go
-- select trancnt=@@trancount
-- select * from t
-- go
-- drop table t
-- go
--
-oj
http://www.rac4sql.net
"harsh" <harshalmistry@hotmail.com> wrote in message
news:OrhD$oe9DHA.1936@TK2MSFTNGP12.phx.gbl...
> I have the following code
>
> begin transaction test
> some inserts
> set @Error=@@error
> if @error=0
> begin
> some updates
> set @error=@@error
> end
> if @error =0
> begin
> commit transaction test
> end
> else
> begin
> rollback transaction test
> end
>
>
> now if there is some error in the execution like
>
>
> begin transaction test
> some inserts
> set @Error=@@error
> if @error=0
> begin
> some updates
> set @error=@@error
> end
>
> select * from not_existing_table
>
> if @error =0
> begin
> commit transaction test
> end
> else
> begin
> rollback transaction test
> end
>
> now the select query will give an error since the table does not exist
> and the process is terminated with an error saying
>
> "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 0, current count = 1."
> how to avoid this?
>
> any help highly appreciated.
> regards,
> harshal
>
>
- Next message: oj: "Re: problem with OSQL in a batch file"
- Previous message: Prabhat: "Re: Need Help in QUERY"
- In reply to: harsh: "TRANSACTION ERROR"
- Next in thread: harsh: "Re: TRANSACTION ERROR"
- Reply: harsh: "Re: TRANSACTION ERROR"
- Reply: harsh: "Re: TRANSACTION ERROR"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
