SEO

#1 (**permalink**) 02-29-2008, 01:31 PM

Let's say I may possibly use two transactions in a script, the second
one will depend on the successful execution of the first one.

The following code works. However, I'm wondering if SQL Server 2000
has some internal function like @@transaction_status to indicate the
status of the most recent transaction by the connection. The analogue
of @@FETCH_STATUS. Then, my own error tracking code could be omitted.

Thanks.

-- ENV: SQL Server 2000
-- ddls
create table tblA (col1 smallint, col2 smallint)
create table tblB (col1 smallint, col2 smallint)
create table tblX (col1 char(1), col2 varchar(20))

declare @errorCode tinyInt
set @errorCode = 0

begin transaction fTran
insert into tblA
values (7,1);

insert into tblB
values (8,0);

-- we know this guy will fail
insert into tblX
values ('ab','abcdefge')

If (@@error <> 0)
begin
select @errorCode = 1
end

if (@errorCode = 1)
rollback transaction fTran
else
commit transaction fTran

if (@errorCode = 0)
pseducode: start second transaction here ...
else
print 'fTran failed.';
RETURN

#2 (**permalink**) 02-29-2008, 01:31 PM

NickName wrote:
> Let's say I may possibly use two transactions in a script, the second
> one will depend on the successful execution of the first one.
>
> The following code works. However, I'm wondering if SQL Server 2000
> has some internal function like @@transaction_status to indicate the
> status of the most recent transaction by the connection. The analogue
> of @@FETCH_STATUS. Then, my own error tracking code could be omitted.
>
> Thanks.
>
> -- ENV: SQL Server 2000
> -- ddls
> create table tblA (col1 smallint, col2 smallint)
> create table tblB (col1 smallint, col2 smallint)
> create table tblX (col1 char(1), col2 varchar(20))
>
> declare @errorCode tinyInt
> set @errorCode = 0
>
> begin transaction fTran
> insert into tblA
> values (7,1);
>
> insert into tblB
> values (8,0);
>
> -- we know this guy will fail
> insert into tblX
> values ('ab','abcdefge')
>
> If (@@error <> 0)
> begin
> select @errorCode = 1
> end
>
> if (@errorCode = 1)
> rollback transaction fTran
> else
> commit transaction fTran
>
> if (@errorCode = 0)
> pseducode: start second transaction here ...
> else
> print 'fTran failed.';
> RETURN
>

I admit I haven't read your post in detail, but essentially you have to
deal with error handling in your own code; @@ERROR is the usual way to
do that, and also check out @@TRANCOUNT in Books Online. See here for
more details about error handling:

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Simon

#3 (**permalink**) 02-29-2008, 01:31 PM

Simon,

Yes, I forgot to add, I've looked at @@TRANCOUNT and tested it, its
output is not consistent in my my testing of a same script (more
complex than the above), so, my take is @@TRANCOUNT is not analogous
to @@fetch_status at all.

Thanks.

Don

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Transaction Management