Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: Cancel a script in Query Analyzer (SQLS2000)

    I'm trying to figure out how to cancel the execution of a script running in QA. The script contains table and SP creation from Ent. Mgr.'s right-clicking "Generate script", in addition to some extra code of my own. One such extra code item is pointing the script to a target database with "USE myDatabase".

    The script is intended to run against "myDatabase", but if the DB doesn't exist, an error is shown in QA and the rest of the script runs against the DB selected in the dropdown list of databases in QA. So if I selected a blank DB called "Junk" in QA, and "myDatabase" doesn't exist on the SQL server, the tables and SPs are created in DB "Junk". This is not desireable.

    I've tried the traditional error checking, but "RETURN" is not cancelling the script:

    Code:
    USE myDatabase
    GO
    IF @@ERROR <> 0 
        BEGIN
        PRINT 'ERROR: Unable to access the target database.'
        RETURN
        END
     
    -- More script to create tables and SPs here.
    I'm assuming it has something to do with:
    a) You can't use return outside a Stored Procedure
    b) All of the GO statements make each part of the script independent mini-scripts

    I'm not sure now to cancel the whole darned thing on an error. I even tried this:

    Code:
    USE myDatabase
    GO
    IF @@ERROR <> 0 GOTO ErrorTrap
     
    -- code to create tables, with all of the GO statements
     
    ErrorTrap:
        PRINT 'ERROR'
    However, I get a message that label "ErrorTrap" is referenced but doesn't exist. Again, because of the GO statements?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    BEGIN TRAN
    
        <do stuff>
    
    ROLLBACK TRAN
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    How would that work out if the label isn't recognized, assuming I'd use a label "ErrorTrap" for the ROLLBACK TRANS statement. Also, I can't see how I would avoid hitting the code in the label if RETURN doesn't seem to affect the script. Can I just remove all of the GO statements to have the labels/RETURNs usable?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you are correct. SQL Server interprets things in terms of batches. A single script can be comprised of one or more batches. These are normally separated by the terminator "GO", but this is configurable on the client. Each batch is a separate execution unit. They are parsed and executed individually. This will show the correct time:
    Code:
    blatant syntax error
    go
    select getdate()
    Variables and labels will not be carried over to the next batch. However, you can use OSQL/SQLCMD (a command line tool) to include the EXIT keyword.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •