Aborting Subsequent Scripts

SeanGough

Currently, when we want to abort running subsequent scripts, we raise an error and rely on the "stop executing on database with error" setting. This works but has to be reviewed and compared against legitimate errors. Is there a cleaner way to tell SQL Multi-Script not to run any more scripts against the current database?

Jon_Kirkwood

Hi @SeanGough

 

Thank you for reaching out on the Redgate forums regarding your SQL Multi-Script cancel query.

 There isn't any special way to cancel script execution in Multi-Script. Hitting the cancel button will stop the currently executing script/s and would rely on any rollback settings you have in your scripts to prevent errors from occurring.

 

Scripts are executed in the order they are displayed in the 'Scripts to Execute' left-hand pane. 

In a single-database deployment, the scripts will execute in order, waiting for the previous one to complete. If you cancel in the middle of an executing script then the subsequent scripts should not have started executing; however, it may result in the partial execution of batches within a script. 

https://documentation.red-gate.com/sms/executing-scripts

 

 If you are deploying scripts against multiple databases there may be an option worth investigating, in Application Options > Optimization there is the ability to define serial or parallel execution. 

Serial execution will start on database A and execute all scripts in order, then repeat the process on database B,C,D etc until all scripts are executed.

Parallel execution will start executing scripts simultaneously databases A,B,C,D... up to the limit you define. If you cancel Multi-Script running in this mode it will likely have different states of script running on different databases.

 

Further details on these database options can be found here:

https://documentation.red-gate.com/sms/configuring-sql-multi-script#ConfiguringSQLMultiScript-Settingdatabaseoptions

 

 Hope this information helps with your use case and allowing for clearer cancellation of running scripts

 

 

SeanGough

Thanks, Jon. I have been using MultiScript since launch, so I am familiar with manual cancellation and parallel execution. I am not looking for a manual cancellation but rather a gate check and abort. The way we do it now is the first script checks certain criteria in the database, and if they are not met, it raises an error, which prevents all subsequent scripts from executing against that database. This works but makes distinguishing between abort and true error scenarios tricky, so I wondered if there was a more graceful way to do it. It sounds like there isn't, so we'll keep using this method. Thanks again for the response!