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?

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.

Solution 1

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.

Solution 2

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.

Solution 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

Solution 4

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.

Solution 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

View more solutions

Comments

[TallGuy]

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.

[Jose Basilio]

In that case, then the TRY/CATCH block should be enough

[Jeremy]

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

[BlackTigerX]

do not use go statements, just enter the statements one after another

[abatishchev]

In case of try/catch you can't use GO.

[abatishchev]

Also any error during a transaction will cancel it but not rollback so you will get one error message more, afaik

[Chizaram Chinedu]

Might not be the right answer to the question but it was the answer I was looking for ;-)

[crokusek]

@BlackTigerX The go's may be needed for things like alter view which must be first statement of a block.

[RBerman]

Also can't be done right if anything called by the outermost code has a transactions, since nested transactions aren't real in SS.

[RBT]

For future readers: SQLCMD Mode - In SQL Server Management Studio what is SQLCMD mode?

[Rono]

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;