How do I get SQL Server Management Studio to stop processing on an error?

Question

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?

Thanks.

Answer 1

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

Answer 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

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 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.