THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

STOP! Wrong Server!

Some time ago I wrote a post about old-time T-SQL flow control. Part of the idea was to illustrate how to make scripts abort on errors, which is surprisingly convoluted in T-SQL. Today I have a more specific example: how can I check that we are really about to execute in the right database on the right server, and halt if not. Perhaps you’ve connected to the wrong server, or the database you intend to modify doesn’t have the name your script expected. “USE database” is wonderful, but what if it didn’t work?

As in the old article, there are two problems: 1. How do I stop inside the batch that is currently running (i.e. between “GO” batch terminators). 2. How do I stop the client from merrily sending the rest of the batches in a whole script to the SQL Server.

There are a number of ways to do this, but there is one method I have found to be clear:

  1. Run in SQLCMD mode, in order to take advantage of :ON ERROR EXIT. This stops the client from transmitting more SQL batches after a batch has thrown an error.
  2. Wrap a check for the server name and database name in a TRY/CATCH at the top of the script. For most practical purposes this can be the first batch, only, since we are planning to halt if the test fails. For versions 2005 to 2008 R2, this has to use RAISERROR, but for 2012 (only) we can finally take advantage of THROW.
:ON ERROR EXIT

BEGIN TRY
    IF ( SELECT @@SERVERNAME ) != 'TheRightServer' RAISERROR( 'WHOA! WRONG SERVER!', 16, 1 );
    USE [master]
    IF ( SELECT DB_NAME() ) != 'TheRightDB' RAISERROR( 'WHOA! WRONG DATABASE!', 16, 1 );
    PRINT 'Rest of First Batch Ran';
END TRY
BEGIN CATCH
    IF ERROR_MESSAGE() = 'WHOA! WRONG SERVER!' RAISERROR( 'WHOA! Caught: WRONG SERVER!', 16, 1 );
    ELSE IF ERROR_MESSAGE() = 'WHOA! WRONG DATABASE!' RAISERROR( 'WHOA! Caught: WRONG DATABASE!', 16, 1 );
    ELSE RAISERROR( 'An exception was thrown in the first batch', 16, 1 );
END CATCH
GO

PRINT 'Second Batch Ran';
GO

Breaking this down – if I am in the wrong server or the wrong database, what I want to have happen is that the statement

PRINT ‘Rest of First Batch Ran’ ;

should not execute. The way to accomplish that is to raise an error if we are in the wrong server or database, and use the TRY/CATCH to transfer execution down to the CATCH block. A sysadmin could raise a more severe error that would stop batch execution, but that’s less friendly, and we can’t assume we are all sysadmins. To me this method needs to work with error severity 16, for a typical user.

Further, it’s important that remaining batches in the whole script are not executed. (Remember, batches are separated by “GO” and are each like a mini program sent from the client to the server.) That means that

PRINT ‘Second Batch Ran’;

should not execute if the first batch threw an error.

The only practical way to do that is with the

:ON ERROR EXIT

directive, but that only works in SQLCMD mode.

For SQL Server 2012, and in the future “and higher,” this example can be simplified using THROW, as:

:ON ERROR EXIT

BEGIN TRY
    IF ( SELECT @@SERVERNAME ) != 'TheRightServer' THROW 50000, 'WHOA! WRONG SERVER', 1 ;
    USE [master]
    IF ( SELECT DB_NAME() ) != 'TheRightDB' THROW 50000, 'WHOA! WRONG DATABASE!', 1 ;
    PRINT 'Rest of First Batch Ran';
END TRY
BEGIN CATCH
    THROW;
END CATCH
GO

PRINT 'Second Batch Ran';
GO

Safe computing!

Published Friday, July 26, 2013 10:26 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kevin Devine said:

I have a couple of scripts that refresh the development environment from production that use this type of syntax at the top.  While I don't have write permission in most of the production databases, there might be something funky with the cross-server setup that could cause a lot of problems.  Better safe than sorry (and restoring databases...)

July 27, 2013 7:10 PM
 

Steve Morris said:

Within TSQL you should be able to use SET XACT_ABORT ON ?

If you use RAISERROR with Severity >=20 then the Connection will be terminated (& no further commands run)

July 28, 2013 4:10 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement