34

This seems like an incredibly dumb question to have to ask, but how do I get SQL Server Management Studio to stop processing a SQL script when it encounters an error?

I have a long script and if there is an error at the start SSMS reports it and then blindly carries on, screwing up things even more. I can't use transactions because the script creates several databases and they can't go in a transaction. It is the database creation that sometimes fails.

Toad for SQL Server will pause at the first error it encounters and ask if you want to stop or continue. This is the behaviour I'm looking for. Does it exist in SSMS?

I am not asking, "How do I write or modify a script so that it stops on an error?" I'm not interested in modifying my script to make this happen, I just want SSMS to stop on an error. Toad for SQL Server does exactly this and that is the behaviour I want. This is also not a duplicate of 659188 because that relates to modifying the script to stop SSMS.

Community
  • 1
  • 1
TallGuy
  • 1,873
  • 6
  • 22
  • 35
  • It's blindly doing what you told it to do! Did you _tell_ it to stop on an error? Would the same script stop on error if run from ADO.NET or from some other script execution tool? The problem is your script, not SSMS. – John Saunders May 11 '09 at 00:07
  • 8
    Most modern high-level execution environments immediately halt on error. Unless explicitly _told_ not to halt or _told_ to branch to error-handling statements on error. – yfeldblum May 11 '09 at 00:11
  • Assuming that's true (and if I cared, I'd ask you to back that up), what's it got to do with the question, which is about SQL Server. The semantics of a batch of T-SQL Statements don't include stopping on error without being told to do so. These semantics don't change simply because "most modern high-level execution environments immediately halt on error". – John Saunders May 11 '09 at 00:15
  • 17
    None of these comments are helpful guys. He's just asking where the settings are that would produce this kind of behavior. – Spencer Ruport May 11 '09 at 00:18

9 Answers9

11

Short answer: You can't.

Thanks to those that provided workarounds, but it seems that SSMS itself can not be set to pause or stop on an error in the same way that Toad for SQL Server can.

TallGuy
  • 1,873
  • 6
  • 22
  • 35
  • 1
    I don't think this is true. It's not the tool that's supposed to handle this, it's the script. You need to use TRY CATCH blocks or IF @@ERROR statements – Jeremy Jul 06 '09 at 23:06
  • do not use go statements, just enter the statements one after another – BlackTigerX Aug 19 '09 at 22:43
  • @BlackTigerX The go's may be needed for things like alter view which must be first statement of a block. – crokusek May 24 '17 at 00:22
7

consider using the command line program 'sqlcmd' that comes with SQL Server, with the -b and the -V options set. -b will cause sqlcmd to quit when it hits an error. -V controls the severity level that is considered to be an error.

Phil Dennis
  • 381
  • 2
  • 5
6

11 years later SSMS still doesn't have this feature...

BUT! You can enable SQLCMD mode (Menu/Query/SQLCMD Mode) and then in text editor you can define this option:

:ON ERROR EXIT

before your t-sql script. Now it will stop execution on error.

Ruslan K.
  • 1,912
  • 1
  • 15
  • 18
  • For future readers: SQLCMD Mode - [In SQL Server Management Studio what is SQLCMD mode?](https://stackoverflow.com/q/9097109/465053) – RBT Dec 29 '21 at 05:28
6

ApexSQL Script generates batch scripts in exactly the manner you want. As an example:

--Script Header
begin transaction
go

{Statement #1}
go
--Standard Post-Statement Block
if @@error <> 0 or @@trancount = 0 begin
    if @@trancount > 0 rollback transaction
    set noexec on
end
go

{Statement #2}
go
--Standard Post-Statement Block
if @@error <> 0 or @@trancount = 0 begin
    if @@trancount > 0 rollback transaction
    set noexec on
end
go

--Script Footer
if @@trancount > 0 commit transaction
go
set noexec off
go
yfeldblum
  • 65,165
  • 12
  • 129
  • 169
5

You need to wrap your SQL Statements inside a Transaction.

BEGIN TRANSACTION
   /* run all your SQL statements */
COMMIT TRANSACTION

If there's an error inside the begin/end transaction, all statements will be rolled back.

EDIT: Wrapping inside inside begin/end transaction, will prevent the statements from getting committed to the database, but not stop it at that point. You need to additionally wrap it inside a try/catch block as follows:

BEGIN TRY
  BEGIN TRANSACTION
  /* run all your SQL statements */
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH
Jose Basilio
  • 50,714
  • 13
  • 121
  • 117
  • 1
    I can't. The script creates several databases and they can't go in a transaction and it is the database creation that sometimes fails. – TallGuy May 11 '09 at 00:09
  • 1
    In that case, then the TRY/CATCH block should be enough – Jose Basilio May 11 '09 at 00:14
  • 6
    In case of `try/catch` you can't use `GO`. – abatishchev Dec 12 '11 at 16:23
  • Also any error during a transaction will cancel it but not rollback so you will get one error message more, afaik – abatishchev Dec 12 '11 at 16:24
  • Might not be the right answer to the question but it was the answer I was looking for ;-) – gnuchu Sep 04 '13 at 16:48
  • Also can't be done right if anything called by the outermost code has a transactions, since nested transactions aren't real in SS. – RBerman Oct 04 '21 at 20:07
  • If you want to know the error message that sent it to the catch block, add this after ROLLBACK TRANSACTION: SELECT ERROR_MESSAGE() AS ErrorMessage; – Rono Jan 04 '22 at 16:22
3

Wow. That's kinda rubbish isn't it? I use SQL-Workbench which, like Toad for SQL Server, handles this easily. Unlike Toad for SQL Server though, it's free.

I'm astonished that such fundamental functionality isn't part of the standard tool.

2

There are a few more work-arounds mentioned here:

SQL Server - stop or break execution of a SQL script

and

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

(raiseerror 20 with log, set noexec on, sqlcmd mode :on error exit, etc.)

Community
  • 1
  • 1
user423430
  • 3,654
  • 3
  • 26
  • 22
2

would using a try catch block help here. On error the try will be exited, implement error handling in the catch

http://msdn.microsoft.com/en-us/library/ms179296.aspx

Stuart
  • 11,775
  • 6
  • 33
  • 31
0

If you can't put your script into a stored procedure and use the return statement to exit on error, the solution provided by @Justice might be your best bet. Everyone else is missing the point - you can't return from a script, even if you use transactions or even if you raiserror. SSMS will just execute the next thing anyway, even if set xact abort is on.

If you can convert your script to a stored procedure, then you can just return from it when you detect an error.

Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145