Transaction Error - SQL Server Forums

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

Transact-SQL (2000)

Transaction Error

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-18 : 01:26:53

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

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-18 : 01:49:44

Search and you shall find

OS

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-18 : 02:07:15

quote:
Originally posted by mohdowais

Search and you shall find

OS

u r right, I tried searching for it but could not find a solution, my situaltion is not regarding nested transactions its just a simple transaction
what if there is an error in the transaction before the transaction is comitted or rolled back, and this error is not trapped
like selecting from an invalid table which throws the control out.In this case though the @error variable will contain non zero value but,
set @error =@@error statement is not executed at all.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-18 : 07:00:53

Hmmm... you are right, attempting to select from a non-existent table seems to be one of those uncatchable errors that aborts the procedure, leaving transactions dangling in mid-air. I am sure someone else here has a solution to this, but I would highly recommend checking for the existance of the table before doing a select, etc on it. The usual check should work:

if exists (select * from dbo.sysobjects where id = object_id(N'[MyTableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

There is no cure like prevention

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-18 : 23:37:36

quote:
Originally posted by mohdowais

Hmmm... you are right, attempting to select from a non-existent table seems to be one of those uncatchable errors that aborts the procedure, leaving transactions dangling in mid-air. I am sure someone else here has a solution to this, but I would highly recommend checking for the existance of the table before doing a select, etc on it. The usual check should work:

if exists (select * from dbo.sysobjects where id = object_id(N'[MyTableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

There is no cure like prevention

OS

I think I have got a way to handle it, the above solution that you provided checks only for the existence of the table in the database, but what if the where clause gives an error if the data in a few rows is invalid giving an error during the comparision? this was the situation I was facing, and found that, such a situation can be handled by using:
exec('select columns from table_name where condition')
this seems to trap the error and the transaction is not left in mid air.

Regards,
harshal.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-18 : 23:55:24

Have you looked at SET XACT_ABORT ON?

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources