Posted by Simon Hayes on 10/01/69 11:28

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

• Next in forum: Re: resultset of proc in another proc
• Prev in forum: Re: Query Help, thanks!
• Thread view: Re: Transaction Management

[Reply to this message]