Query error handling

Here's what I am trying to do:
I have a large SQL script that I need to run often. I want to be able to
run it and get a summary of the errors that occurred (possible info - where
the error occurred, etc.). If at all possible I would like to stop
execution of the script at the point of error as well.
What kind of strategies are available to accomplish this?
Currently I am using Query Analyzer and can output the results to a text
file, but I still need to scan the text file with grep to pull meaningful
entries. Are there any good third party query tools?
Thanks in advance for any advice,
Greg
Greg Michalopoulos wrote:
> Here's what I am trying to do:
> I have a large SQL script that I need to run often. I want to be
> able to run it and get a summary of the errors that occurred
> (possible info - where the error occurred, etc.). If at all possible
> I would like to stop execution of the script at the point of error as
> well.
> What kind of strategies are available to accomplish this?
> Currently I am using Query Analyzer and can output the results to a
> text file, but I still need to scan the text file with grep to pull
> meaningful entries. Are there any good third party query tools?
> Thanks in advance for any advice,
> Greg
You need to check after each statement. SQL Server 2005 offers a
TRY..CATCH mechanism.
-- Run statement
If @.@.error != 0
Begin
Raiserror ('This error occurred here', 16, 0)
Goto Error
End
Return 0
Error:
Print 'batch terminated'
Return 1
David Gugick
Imceda Software
www.imceda.com