Elementary T-SQL Question

From: News (n.sutter_at_comcast.net)
Date: 06/04/04 

Date: Fri, 4 Jun 2004 13:09:47 -0500

Hi,

Probably an easy solution, but I can't find it.

Trying to find a way to create a SQL script that either completes
successfully or stops executing the rest of the code in the source file when
an error occurs. Most of these scripts update a single table in some
fashion. Occassionally a script that works in one location won't work at
another site; so a graceful error detection and exit is desired. (The source
files containing each script are executed via ISQL. And multiple sequential
calls to ISQL are linked together in a CMD file.)

Each SQL script generally does something like the following:

Drop the existing backup table, if it exists
Create a backup table structured like the current primary table
Populate the backup table from the primary table
Drop the primary table
Re-create the primary table (new columns, re-defined or re-ordered columns,
whatever)
Populate the new primary table from the backup table
Drop the backup table.

All of this code is in a single input source file. But it is more than one
batch as there are 'GO's between each of the above statements. This sequence
works fine, .... as long as it works. But when an error occurs, then I'd
like to be able to detect the error and stop processing the rest of the
source file.

I've tried testing for @@ERROR after each statement and using RETURN xxx
when an error is detected, but the compiler doesn't allow numeric returns
unless in a SP.

Besides the return statement apparently only exits the batch it is contained
in; it doesn't stop processing the rest of the source file.

I've tried using local variables in the source file, but, again, these seem
to be local to the batch only, not the duration of the source file.

So, what have I missed.

Thanks for any help you can offer.

Neil Sutter

Relevant Pages

  • help with drag-and-drop "problem"
    ... First off, this script runs perfectly well as is, and compiles ... Note that I have not got around to actually attempting to compile the source file dropped on the script, the script is still set up to compile the built-in source file name. ... Shared Sub Main ...
    (microsoft.public.scripting.wsh)
  • Re: Elementary T-SQL Question
    ... If you execute the script using OSQL.EXE, you can RAISERROR with a *state* of 127, which makes OSQL to exit ... Tibor Karaszi, SQL Server MVP ... > Create a backup table structured like the current primary table ... > All of this code is in a single input source file. ...
    (microsoft.public.sqlserver.programming)
  • Re: OT: how to get make to run a script before each build
    ... > source file has changed). ... or something to that effect, key being, change/override the compilation rule ... Problem with today's modular software: ... Sorry, I was asking for the make rule, not the script. ...
    (freebsd-questions)
  • Re: invoking gdb from within an application script
    ... I want to debug a C ... program which is being called from inside an application script. ... But since it does not stop at gdb ... command does not function as the source file path at the development ...
    (comp.unix.shell)
  • Feedback on backup script
    ... The one problem I have is that the script keeps stopping at lines 633,635 and or 637 with the error "Path Not Found" I have emailed the original author for help but haven't heard back yet. ... I was just wondering if the problem could be that the account running the script doesn't have correct permissions to a share/directory/file that it's trying to backup? ... ' always one less than the actual number of folders. ... ' Show the user free space info on all available disk drives ...
    (microsoft.public.scripting.vbscript)
Loading