Rollback Tran Fails - 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)

Rollback Tran Fails

Author

Topic

ppetrou
Starting Member

4 Posts

Posted - 2003-06-16 : 09:32:52

Hi,

I am having a SELECT statement to a non existent table in my
transaction.

It seems that the the execution stops there and does not
reach my IF BLOCK where I check the @@ERROR and either
COMMIT or ROLLBACK the transaction.

The invalid table is there just to test if the ROLLBACK works
if sth goes wrong.

I do inserts to other tables. After it reaches the SELECT statement
it will not ROLLBACK and comes up with the following error.

Server: Msg 208, Level 16, State 1, Procedure usp_test_sproc, Line 164
Invalid object name 'aTable'.
Server: Msg 266, Level 16, State 1, Procedure usp_post_acceptdeny_status, Line 65535
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Heres an example (assume the CREATE PROC , DECLARATIONS etc are there)

BEGIN TRAN myTran

INSERT INTO xTable VALUES ....

IF @@ERROR <> 0 THEN
BEGIN
SET myError = @@ERROR
END

INSERT INTO xyTable VALUES ....

IF @@ERROR <> 0 THEN
BEGIN
SET myError = @@ERROR
END

INSERT INTO xyzTable VALUES ....

IF @@ERROR <> 0 THEN
BEGIN
SET myError = @@ERROR
END

SELECT * FROM aTable -- This is the non existent table

IF @@ERROR <> 0 THEN
BEGIN
SET myError = @@ERROR
END

IF myError <> 0 THEN
BEGIN
ROLLBACK TRAN TranStart
RETURN 1
END
ELSE
BEGIN
COMMIT TRAN TranStart
RETURN 0
END

Any ideas why it does not ROOLBACK? All the entries from the
insert statements are there.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-16 : 10:03:13

Hi:

The problem you are having is due the fact that @@ERROR is set at every T-SQL Statement. Hence you need to trap @@ERROR first into the variable, and then do the comparision.

BEGIN TRAN myTran

SELECT * FROM aTable -- This is the non existent table

~~IF @@ERROR <> 0 THEN~~ --here it contains an error code
~~BEGIN~~ --by this time @@ERROR is 0
SET myError = @@ERROR
~~END~~

IF myError <> 0 THEN
BEGIN
ROLLBACK TRAN TranStart
RETURN 1
END
ELSE
BEGIN
COMMIT TRAN TranStart
RETURN 0
END

Owais

ppetrou
Starting Member

4 Posts

Posted - 2003-06-16 : 11:15:02

Thanks very much for this, it was a very good point.

Although it does not seem to solve the problem.

I tried this simple code and I get the same error.

The row in the testTable is still there after I
execute the sproc. I cant even open the table from
Enterprise Manager (i do the select in query analyzer)
unless i execute a rollback tran TranStart manually
from sql analyzer to finish the transaction.

I think the execution stops just after the invalid select.
Thats why i think it complains about the missing COMMIT ROLLBACK statement.

Any ideas?

(AGAIN assume that the create procedure etc statements are there)

DECLARE @intError int

BEGIN TRAN TranStart

INSERT INTO testTable VALUES (1,'','','','')

SELECT * FROM aTable -- Non existent

SET @intError = @@ERROR

-- which should be 266.
-- I thought it would be 208 from the invalid object
-- but it keeps keeps going and reassigns the @@ERROR
-- with the last value.

IF @intError <> 0
BEGIN
ROLLBACK TRAN TranStart
RETURN 1
END
ELSE
BEGIN
COMMIT TRAN TranStart
RETURN 0
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-16 : 12:36:23

You need to remove the SELECT statement for the nonexistant table. You must put @@ERROR into a variable immediately after the INSERT statement. The way that you are doing it right now is that the error for the SELECT statement is being put into the variable instead of the error for the INSERT statement.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-16 : 12:53:49

Tara:
Thats exactly what he is trying to do...the INSERT statement doesnot raise an error, its the SELECT statement. But that's not happening.

ppetrou:
Try this

SET XACT_ABORT OFF

DECLARE @intError int

BEGIN TRAN TranStart

INSERT INTO testTable VALUES (1,'','','','')

SELECT * FROM aTable -- Non existent

SET @intError = @@ERROR

IF @intError <> 0
BEGIN
ROLLBACK TRAN TranStart
RETURN 1
END
ELSE
BEGIN
COMMIT TRAN TranStart
RETURN 0
END

Setting XACT_ABORT to OFF will prevent SQL Server from automatically terminating and rolling back a transaction in the case of an error. Which is why you were not able to get to the IF intError...block. Use this setting with care, only if you are going to be handling errors after every statement.

Owais

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-16 : 13:05:18

If you reference a table that doesn't exist then the query cannot generate a query plan as it cannot resolve the objects therefore it cannot run at all.
Your SP will stop at the time the server tries to generate the plan for the select statement and will not process any further - it will never get to the error trapping.

I would expect the SP not to run at all but I have seen cases where it runs a bit so I guess the plan is not always created completely before the SP runs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-16 : 13:19:07

Thanks Nigel. I knew it was something like that.

Ppetrou, why don't you check if the table exists before the select statement? IF EXISTS (SELECT name FROM sysobjects...)

Also, why are you even selecting from a table that doesn't exist? What is the real requirement here?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-16 : 14:23:16

quote:

Ppetrou, why don't you check if the table exists before the select statement? IF EXISTS (SELECT name FROM sysobjects...)

Still won't help as the optimiser will still generate an error.
It would work if the select was in dynamic sql though.

This would never be a problem in the good old days when the SP wouldn't have compiled.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ppetrou
Starting Member

4 Posts

Posted - 2003-06-17 : 04:15:12

Hi All,

Thanks for your help. It is now clear what is going wrong.

As I said to my first post the only reason I put the invalid table
there is to test if the rollback works. I could not find any other
way to raise an error. Everything started when I accidentaly did
a select to an invalid table and saw that the rollback did not
work. Then I started being worried that my sproc is not correct
as I had never tried out to do a rollback.

I had no idea about the SET XACT_ABORT OFF and now I get what is
happening with the query plan.

If anybody knows a way to raise an Error so I can properly test
a Rollback in the future I would be grateful.

Thanks a lot

Petros

ppetrou
Starting Member

4 Posts

Posted - 2003-06-17 : 04:39:26

Hi, again

i just found the RAISERROR.

I would be able to test ROLLBACKS with this in the future.
Please let me know if there is a better way.

Thanks

Petros

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources