Different behaviour SQL server 2000 / 2005

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)

Different behaviour SQL server 2000 / 2005

Author

Topic

erde
Starting Member

6 Posts

Posted - 2006-10-11 : 11:01:30

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

Kristen
Test

22859 Posts

Posted - 2006-10-11 : 14:31:17

No TRY CATCH operating in SQL 2005 presumably (you do see the "TRAN 3" transaction count in SQL2k5 presumably?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources