SQL Server: How to abort a series of batches in Query Analyzer?

Question

i have a series of T-SQL statements separated by the special Query Analyzer batch separator keyword:

GO

If one batch fails, i need Query Analyzer to not try subsequent batches - i want it to stop processing the series of batches.

For example:

PRINT 'This runs'
go

SELECT 0/0, 'This causes an error'
go

PRINT 'This should not run'
go

Output:

This runs
Server: Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
This should not run

Possible?

---

Update

An example of this in real use might be:

 sp_rename 'Shelby', 'Kirsten'
 go

 DROP VIEW PeekAView
 go

 CREATE VIEW PeekAViewAS 
 SELECT * FROM Kirsten
 go

Apotropaic

did any of these answers work for you? – Lorgnon

This is a very valid question, why are there so many downvotes? – Impressionable

Answer 1

Here is how I'd do it:

PRINT 'This runs'
go

SELECT 0/0, 'This causes an error'
go
if (@@error <> 0)
    Begin
    set nocount on
    set noexec on
    End
GO

PRINT 'This should not run'
go

set noexec off
set nocount off
GO

The "noexec" mode puts SSMS is a state where it just compiles the T-SQL and doesn't actually execute it. It is similar to accidentally pressing the Parse toolbar button (Ctrl+F5) instead of Execute (F5).

Don't forget to turn noexec back off at the end of your script. Otherwise users are going to get confused by permanent "Command(s) completed successfully." messages.

I use the check against @@error in the subsequent batch instead of using TRY CATCH blocks. Using @@error in the next batch will catch compile errors, like "table doesn't exist".

In addition to the noexec mode, I also toggle the nocount mode. With noexec mode on and nocount off, your queries will still report a message "(0 rows(s) affected)". The message always reports zero rows, because you're in noexec mode. However, turning nocount on suppresses these messages.

Also note that if running SQL Server 2005 the command you are skipping might still give error messages if it references a table that doesn't exist and the command if the first command in the batch. Forcing the command to be the second command in the batch with a bogus Print statement can suppress this. See MS Bug #569263 for more details.

Silma

In the years since i asked this question i learned that an SQL diff tool (not SQL Compare) does this. After every statement check if there is still a transaction in progress, and if not SET NOEXEC ON – Apotropaic

It does not work subsequent batches are run. If you had query that create tables instead of printing to screen in penultimate batch it would create the table. So the code is wrong. – Divinity

Trismegistos, I replaced the "PRINT 'This should not run'" with a Create Table statement and the table is not created. Can you post an example? – Silma

+1 Very elegant. I am now using this in a server backup script that loops through all databases: if a test against one database fails, subsequent statements against it are wrapped up with this 'noexec' approach. Some of the batched statements successfully being skipped include: xp_create_subdir, backup log, restore verifyonly. I'd call that a win. Thanks. MS SQL Server 2008 R2. – Interknit

Answer 2

You can activate the "Query, SQLCMD Mode" menu option and place the following at the beginning of the script:

:on error exit

This will stop execution when an error occurs, even if there are subsequent batches.

Just make sure that you don't accidentally run the script without SQLCMD mode on because you will get the typical behavior where errors are ignored.

Xi

Also, errors that are raised (RAISERROR) are not shown in the output. – Picardi

Answer 3

When I need to do this, I issue a RAISERROR of severity 20. This, or higher, will kill the current connection, and prevent subsequent "GO batches" from executing. Yes, it can be awkward, but it does the job.

Century

According to #659688 it needs a 'with log' as well. Either the above suggestion or the 'set noexec on' method mentioned by the linked question are the most elegant ways of achieving this, IMO. – Spineless

Also, this requires sysadmin / alter trace rights, which some people might not have. "Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions." Source: MSDN RAISERROR – Picardi

Answer 4

Create a temporary table; and update it after each step (if successful); and then check the success of the previous step by validating against the table.

create table #ScriptChecker (SuccessfullStep int)

-- Do Step One
Insert into #ScriptChecker
Select 1

-- Step 2
If exists (select * from #ScriptChecker where SuccessfullStep = 1)
-- Do Step 2 ...

Denominator

+1. Perhaps there's a better way but this simply is so easy I wonder why I never thought of that. – Clearsighted

Its more or less how MS do it themselves in the scripting wizard – Denominator

This won't work when one of the statements is CREATE VIEW – Apotropaic

You can check for the existence of the view and use that to control the insert into your transaction table – Denominator

What if you're updating an exiting view? Or a function or procedure or trigger? – Century

Answer 5

based on @u07ch idea, but only insert on failure...

create table #test (failure  int)

if not exists (select * from #test)
BEGIN
    print 'one' --sql here
END
go

if not exists (select * from #test)
BEGIN
    print 'two'--sql here
END
go

if not exists (select * from #test)
BEGIN
    print 'three' ---SQL SERVER 2000 version
    --error--
    SELECT 0/0, 'This causes an error'
    IF @@ERROR!=0
    BEGIN
        insert into #test values (1)
        PRINT 'ERROR'
    END
end 
go

if not exists (select * from #test)
BEGIN
    print 'three'  ---SQL SERVER 2005/2008 version
    BEGIN TRY
    --error--
        SELECT 0/0, 'This causes an error'
    END TRY
    BEGIN CATCH
        insert into #test values (1)
        PRINT 'ERROR'
    END CATCH
END
go

if not exists (select * from #test)
BEGIN
    --sql here
    print 'four'
END
go

output 2000:

one
two
three

----------- --------------------
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.

(1 row(s) affected)

ERROR

output 2005/2008:

one
two
three

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

(0 row(s) affected)

(1 row(s) affected)

ERROR

Lorgnon

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? can someone tell me why there are so many down votes on this question? get a life, they are only "points" and life will go on with or without them ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? – Lorgnon

Isn't it obvious? "This question is not useful or clear". /sarcasm – Apotropaic

<sarcasm>Maybe your answer was too much detailed</sarcasm> Moreover it should also perform better (though slightly) than the original one since it inserts only when there is an error.... Did you flirt with someone's wife, recently? ;-) – Pi

Answer 6

Erland Sommarskog in the microsoft.public.sqlserver.programming group had a very good idea:

In a change script such as the one you posted, you need to be defensive, and start each batch with IF @@trancount > 0.

Using

IF @@trancount > 0 

is much cleaner.

Apotropaic

This won't for for all batch contents. – Apotropaic