Sunday, June 14, 2009

Abort SQL batch

I am working in an environment that has three databases:

database ABC on server ABC - the production system

database ABC_QA on server ABC2 - for QA testing

database ABC_DEV on server ABC3 - for development

The developer writes scripts to create / alter objects & manipulate data during development on ABC_DEV, and then sends them to the DBA to run against ABC_QA for QA testing. When QA is complete, the developer prepares a final script for the DBA to run against ABC. In both cases, the "script" file is a compilation of multiple individual scripts, each of which ends with a GO statement.

A problem arose when the DBA ran an intermediate QA script against production - the script does not include a USE statement; but even if it did, and that failed, the script would continue to run, and any create object statements would execute against whatever the current database connection was (usually master). Not good.

I did some research on RAISERROR and found that a severity level of 20 of higher will actually close the database connection, and thus abort the entire script, preventing any extra objects from being created. Adding the following to the beginning of the script takes care of this:

IF NOT EXISTS
(SELECT 1
FROM sys.sysdatabases
WHERE [name] = 'ABC')
RAISERROR ('INCORRECT DATABASE', 21, 1) WITH LOG

which yields the following message to the DBA:

Msg 2745, Level 16, State 2, Line 5
Process ID 57 has raised user error 50000, severity 21. SQL Server is terminating this process.
Msg 50000, Level 21, State 1, Line 5
INCORRECT DATABASE
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

...and the rest of the script is aborted without any further processing.

No comments: