Fatal Error Handling

Question

Hello,

I am currently writing a T-SQL template that will be used at client sites to update their databases whenever our software product requires backend changes.

The goal of the template is to wrap up all DDL/DML with error handling, and if an error occurs during the execution of the script at a remote site, SQLMail sends our office an email with the time/cause/site/....

A pseudo version of the template is:

 

CREATE TABLE #ERROR_STORE AS xxxxxxxxxxxxxxxx

BEGIN TRANSACTION "xxxxxxxxx"

 

BEGIN TRY

DDL/DML

END TRY

BEGIN CATCH

INSERT INTO #ERROR_STORE (@@ERROR, xxxxxxxxxxxxx)

END CATCH

IF RECORDS_EXIST_IN(#ERROR_STORE) BEGIN

ROLLBACK TRANSACTION

SQLMAIL("send me all errors in #ERROR_STORE")

END

ELSE BEGIN

COMMIT TRANSACTION

END

DROP TABLE #ERROR_STORE

-----------------

The problem with this approach is that any fatal errors will kill the execution of the entire query. So anything like "select * from A_TABLE_THAT_DOESNT_EXIST" will leave me helpless

I need a way (is there a way..) to manage/catch/detect a fatal error that occurs when a script of this nature is executed.

Thanks.

Answer 1

The answer to your question is NO, you cannot trap "table does not exist" by any method other than checking to see if it exists first.

The error handling in SQL 2000 and 2005 is EXTREMELY limited.  This is a HUGE failing of MS to fix.  The TRY/CATCH in 2005 is a step in the right direction, but it only catches a limitted amount of errors, basically the things that set @@ERROR in 2000.

Most SQL errors are TERMINAL and stop the batch from running and you cannot trap them at all.  Worse, if you have a parent stored proc calling a child stored proc, and the child fails, lets say for "table does not exist", the child proc TERMINATES on the line that caused the error, and returns to the parent as if nothing happened.

Answer 2

Yeah, the error handling in 2005 is more oriented to DML errors than DDL errors.  I would look at what RedGate does with their SQL Compare tool as a good idea of how to do things (you can get their tool and look at the output, and use it too, it is a nice tool for building these kinds of differential scripts from version to version.)

Bottom line is that I would consider building a loader program that runs your scripts in an installer-like fashion and probably not just provide scripts for the user to run.  Then you have error handling power at the client level.

Answer 3

Oddly enough, my company uses SQL Compare... I was creating a console app that would clean out a few things I didnt like about it and add in a few bits that I needed (e.g. SQL Mail if errors occurred).

Correct me if I am wrong, but they wrap every DDL/DML statement into its own transaction, so some of the script can commit where other parts of the script could fail... I really dont think they would do this but thats what it looked like in the script...

BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[TimeEntries]'
GO
CREATE TABLE [dbo].[TimeEntries]
(
....
)

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SetTimeEntry]'
GO

etc. etc.

 

Also, I am going to be automating all of this eventually (not passing scripts to client site IT people), so having a loader program isnt out of the question. But how would the error detection improve by going in this direction?

 

Thanks!

Answer 4

Yes, that is what they do.  And roll it back at the end of each of the batches if there has been an error.  This way nothing gets committed, and the next transactions don't get committed either..