It's easy to create new scripts or add existing saved scripts for deployment to databases. You can then specify the order in which SQL Multi Script executes scripts. You can also set how many databases SQL Multi Script executes scripts against simultaneously and the behavior if there is an error during script execution.

Creating new or adding existing scripts

When you first launch SQL Multi Script, a blank script called script1.sql is automatically created for you in the Scripts to Execute pane.

To create additional scripts, click then right-click on the script name to select save options.

To edit a script, select it from the Scripts to Execute pane to display the contents in the Script pane.

To add an existing saved script, click  and use browse options to select it.

Changing the order of scripts

To change the order in which SQL Multi Script executes or parses the scripts you've added to the Scripts to Execute pane, select a script and click or to move the script up or down in the list. The execution order is indicated by the number next to each script. Information or errors are displayed in the Results pane.

Specifying databases

You need to specify the databases against which SQL Multi Script can execute scripts. You do this by creating a database distribution list.

1. In the Database Distribution List pane, click Configure to display the Configure Database Distribution Lists dialog box. 

   Under Distribution Lists, the Default Distribution List is selected by default:

   

   If you want to create your own custom database list or rename an existing list, use the New or Rename buttons.

2. Select a database from the Databases to Add pane and click Add.

   If the database you want isn't displayed in the pane, click Add a SQL Server Not Listed, enter the TCP/IP address or SQL Server name and click Add.
3. Once you've finished adding databases to the list, click OK.

Setting database options

To set how many databases SQL Multi Script executes against simultaneously, from the Tools menu, select Application Options. Under Optimization, select Serial execution or Parallel execution:

• With Serial execution, SQL Multi Script executes all the scripts against one database. When the scripts have been executed against the first database, SQL Multi Script executes all the scripts against the next database, and so on until all the scripts have been executed against all the databases.
• With Parallel execution, SQL Multi Script executes the first script against each database in your list. When a script has been executed on a particular database, SQL Multi Script executes the next script against that database. This means that results for different databases may be returned at different speeds.
  
  • If you choose Parallel execution and you set the Databases to execute against simultaneously option to a number that is the same as or greater than the total number of databases that you are executing scripts against, SQL Multi Script executes all the scripts simultaneously.
  • If you choose Parallel execution and you set the Databases to execute against simultaneously option to a number that is lower than the total number of databases that you are executing scripts against, SQL Multi Script executes all the scripts against the maximum number of databases you have set. As soon as execution against one of the databases is complete, it starts executing the scripts against one of the remaining databases.

You can see the progress of results being returned for each script and for each database in the Results pane.

To force each script to run in its own connection, check the Use a new connection for each script checkbox. This protects against unintended consequences of commands that change behaviour in a way that persists within a connection, for example SET ROWCOUNT.

Saving projects

Projects were introduced in SQL Multi Script version 1.2 and introduce the concept of a project which contain both a distribution list and set of sql files that can be loaded together.

To create a project from the Tools menu, select Save project as... This allows you save the current set of projects and distribution list together in a directory.

Configuring error handling

To configure how SQL Multi Script handles errors while executing, select one of the following options from the On error list (above the Database Distribution List pane):

• Continue executing
  If there is an error with executing part of a script, SQL Multi Script continues executing from the next batch in the script. This does not affect execution of this script against any other databases in the distribution list.
• Skip script on database with error
  If there is an error with executing part of a script, SQL Multi Script does not try to execute any more of this script against this database, but continues executing from the next script on the list. This does not affect execution of this script against any other databases in the Database Distribution list.
• Stop executing on database with error
  If there is an error with executing part of a script, SQL Multi Script does not try to execute any more of this script or any other scripts against this database. This does not affect execution of any scripts against any other databases in the distribution list.
• Stop executing
  If there is an error with executing part of a script, SQL Multi Script stops executing any scripts on any databases.