Stored Procedures and Transactions: COMMIT or ROLLBACK missing 1

[bnagel2]

I'm seeing something strange in Query Analyzer, and I wonder if anyone else has noticed it.

Inside a stored procedure, if I refer to a non-existent table, the proc fails--giving an error, but I'm left with an additional error message indicating that a COMMIT or ROLLBACK is missing.

Some quick steps to reproduce:

CREATE TABLE [tblTest] (
	[TestId] [int] NOT NULL 
)
GO

CREATE      Procedure spTransactionTest 
(@TestInput INT)
As
BEGIN TRANSACTION

INSERT INTO tblTest1 
VALUES (3) 

ROLLBACK TRANSACTION
GO

When I run the stored proc above in Query Analyzer, I get two errors: an error telling me that I typed the wrong table name, and an error telling me that I am missing a COMMIT or ROLLBACK, even though the ROLLBACK is obviously present.

However, if I alter the stored procedure code a little bit:

INSERT INTO tblTest
VALUES ('wow')

using the correct table name, but still generating an error, I only get the single error that I am expecting.

Has anyone else run into this situation? Any idea why it is happening?

Thanks.

Ben

 

[vongrunt]

After you executed stored procedure first time, it left opened transaction on runtime error (@@TRANCOUNT > 0).

Rollback everything, alter procedure after that and try again.

 

[bnagel2]

Thanks vongrunt.

I have restarted Query Analyzer several times, opened up a clean ODBC connection, and I still get the same error!

If you try my sample code above, do you get two errors like I am seeing?


Ben

 

[vongrunt]

I tried the same, got same errors as you, then did ROLLBACK TRAN (checked that with SELECT @@TRANCOUNT, was 0), altered procedure with correct table name - and everything was OK.

QA complains if you try to close it while transactions are still pending.

 

[bnagel2]

Thanks very much for looking at this with me, vongrunt.

I understand what you did, and I see that I can run a ROLLBACK TRAN while transactions are still pending in Query Analyzer.

I guess my point is this: there shouldn't be a transaction pending. ROLLBACK is called in the stored proc immediately after the error.

In fact, if you try my second example above (where I raise an error by inserting text into the INT field), you'll see that an error is raised, the proc quits, the ROLLBACK TRAN code is run, and there is no pending transaction.

I don't understand why some errors trip the ROLLBACK TRAN in the stored procedure when other errors don't. This particular error doesn't cause me much trouble (a little bit of testing ensures that my procs don't use incorrect table names), but I am concerned that I can't find any documentation telling me why one particular error hangs a proc while others don't. I am rolling out an ASP.NET and SQL solution that uses a couple of nested transactions, and I don't want those transactions hanging for reasons I don't understand.

I would really appreciate it if you, vongrunt, or another reader would try my second example and see if you get the same result that I do.

Thanks for your time and collaboration.

 

[vongrunt]

This is one of SQL2K things I don't like either. DB-schema dependent errors (wrong table name for example) terminate sproc, user-dependent errors (wrong data type) don't. Mircosoft provided extra mechanism that rolls back transaction on any error (XACT_ABORT ON), but I'm not sure it will work for first case... again.

As you said, not much trouble - but still not pleasant surprise when you trap over it.

 

[bnagel2]

Thanks very much, vongrunt! Here's a star for you.

As long as I can be confident that DB-schema-dependent errors are the only ones that will behave this way, then I'm good to go.


Ben