October 11, 2006 at 8:10 am
Why is the result (open transactions) of the following script different when executing in sql server 2000 and sql 2005?
With sql server 2000 I have an open transaction, with sql 2005, all transactions are rolled back.
set nocount on
if object_id('test') is not null drop procedure test
go
if object_id('testtable') is null
exec ('create table testtable (id int, status int)')
go
create procedure test as
begin
set xact_abort on
begin tran
update testtable set status=status where id=2006
select * from testtt -- non existing table. --> error.
commit tran
end
go
select 'Tran 1',@@trancount
exec test
select 'Tran 2',@@trancount
go
select 'Tran 3',@@trancount
while @@trancount<>0 rollback tran
if object_id('test') is not null drop procedure test
if object_id('testtable') is not null drop table testtable
go
October 12, 2006 at 7:44 am
Hi,
Its an interesting one, it looks like in SQL Server 2000, the SET XACT_ABORT ON setting only works for where there is a data integrity issue, e.g. wrong data type, key violation. However in SQL Server 2005, it seems to work as expected for both data integrity problems and database structure issues, e.g. wrong table specified. Hope that helps.
October 12, 2006 at 7:46 am
Sounds like a bug to me. If you run it in 2005, any statements after the "exec test" is not executed, which means sql server termininates the whole batch. Since the XACT_ABORT is set in the SP, its scope is within the SP. It should terminate the SP only, NOT the batch.
you can use DBCC Useroptions to verify the scope.
Anyway, it's not suggested setting xact_abort on if you explicitly control your transaction (begin/commit/rollback tran).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply