Joseph Michael Pesch
VP Programming

Microsoft SQL Server Stop Script Execution

by 14. February 2012 14:11

I have two basic styles of scripting to stop a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft stop where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard stop where we do not leave it up to the user.  For the hard stop the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role. So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Stop

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30';

Style 2 - Hard Stop

:setvar SqlCmdMode "DbNameHere"
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 


Comments are closed