Pausing an SQL server script?

Question

Hi, I'm trying to develop a script that I will be able to run on an SQL server 2008 [Express] database. The database contains about 10-15 tables that are being updated with different values / new fields etc.

However, it's not a fully automated process and at points in the upgrade, it would be great to pause execution of the script, put up a messagebox from within the script, allowing the user to do <something>. Clicking OK on the script would continue it's execution and finish the migration.

Does such a thing exist?

Answer 1

You can pause using WAITFOR. I doubt you can actually pop up a messagebox from within a T-SQL script

Answer 2

SQL Server scripts do not support suspending and restarting execution arbitrarily.

The debugging features of the SQL 2008 Management Studio enable you to pause a script at one or more breakpoints, but you will not be able to guarantee that the user is executing your script from that environment.

If you need to pause the script for an undefined amount of time for the user to carry out actions, the easiest way to do so will be to split it across multiple script files.

If you need this to be automated, you could then trigger them from a .cmd file using the DOS PAUSE command between scripts.

Answer 3

There is no direct way to cause a T-SQL script to pause and wait for user input. The WAITFOR command will cause the execution to wait for a specified period of time, but does not allow user interaction.

A (very) crude solution would be to break the process into small script-chunks and control their execution through the a good old fashioned BATCH script. For example: -

SQLCMD -S <servername> -E -I <path_to_script_one>
PAUSE
SQLCMD -S <servername> -E -I <path_to_script_two>

Obviously there are some drawbacks with this approach too; error handling becomes cumbersome and problematic as there is no easy way to determine success or failure. BATCH commands are relatively limited although a different script languages might provide more functionality.

There are security issues if you allow the user to enter values for parameters that result in dynamic SQL statements.

If you want a "rich" experience, you should consider UI built with the tool of your choice...