6

I found and article in the MSDN Lbrary explaining that try/catch does not handle errors thrown when an object cannot be found. SO, even though I wrap a transaction in a try/catch, the rollback phrase will not execute:

BEGIN TRY BEGIN TRANSACTION SELECT 1 FROM dbo.TableDoesNotExists PRINT ' Should not see this' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION SELECT ERROR_MESSAGE() END CATCH --PRINT 'Error Number before go: ' + CAST(@@Error AS VARCHAR) go PRINT 'Error Count After go: ' + CAST(@@Error AS VARCHAR) PRINT 'Transaction Count ' + CAST(@@TRANCOUNT AS VARCHAR)

What's the recommended way to handle errors thrown when an object does not exists, especially when there is a transaction involved. Should I tack this bit of code in place of the last two print statements:

IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'Rolling back txn' ROLLBACK TRANSACTION END go PRINT 'Transaction Count again: ' + CAST(@@TRANCOUNT AS VARCHAR)
Improve this question

3 Answers 3

Reset to default
1

You can test for the existence of an object with OBJECT_ID():

IF OBJECT_ID('MyTable') IS NULL RAISERROR('Could not find MyTable.', 18, 0)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

yes, I could but this is not scalable. Imagine If I'm rolling out a test script that touches 50 objects, do I test for each one? It seems to me that try/catch should handle the error in the first place.
0

Why are you trying to retrieve data from a table which does not exist?

The fundamental building block of a database is a table. Not knowing what is in your schema is essentially trying to use SQL as a dynamic language, which it is not.

I would rethink your design; without knowing more about the tables in your database and its intended use it's hard for others to help in this regard. Add some more information to your question please.

EDIT I've had a read of BOL and the recommended way to handle errors when an object does not exist is as follows:

You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence.

I tested this out using the following procedure

CREATE PROCEDURE [dbo].[BrokenProcedure] AS BEGIN SET NOCOUNT ON; SELECT * FROM MissingTable END GO

Then called it within a TRY..CATCH block:

BEGIN TRY PRINT 'Error Number before: ' + CAST(@@Error AS VARCHAR) EXECUTE [dbo].[BrokenProcedure] PRINT ' Should not see this' END TRY BEGIN CATCH PRINT 'Error Number in catch: ' + CAST(@@Error AS VARCHAR) END CATCH

Resulting in the following output:

Error Number before: 0

Error Number in catch: 208

It's not a perfect solution as you will have to create procedures (or use dynamic SQL) for all of your table access, but it's the method recommend by MS.

Improve this answer

4 Comments

Sometimes in dynamic environments, you don't know the precise schema; another process might be changing the database structure, so it's essential that normal try/catch exception handling should work as normal.
Fair point. Yes, the try/catch handling should work correctly but I wasn't defending MS; I just don't don't agree with this use of a database.
Tony, This scenario comes up in our test and development environment where we have multiple developers working simultaneously on changes that they are making. Now this is a situation I have no control over, so I can't change it. However, since changes can occur to objects in test, I need to be able to handle errors generated from missing objects, and I thought the try/catch mechanism should be able to handle that. Since it doesn't I'm trying to figure out the best way to use transactions so that the changes I'm responsible for don't end up in some sort of inconsistent state.
@gr928x: I've updated my answer to include a way of handling this error. However, it appears your change control process is broken if you have multiple developers modifying DB objects on which your code depends. If you can't change the process try to get a personal copy of the DB to develop your code against and avoid the problem altogether.
0

Now, you've hit on an interesting issue (well, to me anyway). You should not start a transaction because the batch won't compile. However, it can compile and then statement level recompilation fails later.

See this question What is wrong with my Try Catch in T-SQL?

However, in either case you can use SET XACT_ABORT ON. This adds predictability because one effect is to automatically roll back any transaction. It also suppress error 266. See this SO question too

SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION SELECT 1 FROM dbo.TableDoesNotExists PRINT ' Should not see this' COMMIT TRANSACTION END TRY BEGIN CATCH -- not needed, but looks weird without a rollback. -- you could forget SET XACT_ABORT ON -- Use XACT_STATE avoid double rollback errors IF XACT_STATE() <> 0 ROLLBACK TRANSACTION SELECT ERROR_MESSAGE() END CATCH go --note, this can't be guaranteed to give anything PRINT 'Error Count After go: ' + CAST(@@Error AS VARCHAR) PRINT 'Transaction Count ' + CAST(@@TRANCOUNT AS VARCHAR)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.