Once you've installed SQL Multi Script, you will have a simple way of executing multiple scripts against multiple SQL Servers. This walkthrough will show you how to complete the task in the first few minutes of using SQL Multi Script. Including:
How to open and create scripts with SQL Multi Script
How to set up a database distribution list to execute scripts against
How to execute multiple scripts against multiple databases with a single click
How to view and save the results
Step 1: Creating and opening a new script with SQL Multi Script
On opening SQL Multi Script, it's easy to create new scripts or add existing saved scripts for deployment to our databases.
To begin with, a new, blank script, script1.sql is created for us. Right-clicking on our new script on the Scripts to Execute pane enables us to save it. To open an existing script we can click Add and browse to find the saved script. On this occasion, we will open an existing script called CreateSchema.sql; this script will create a schema on three databases for us.
Step 2: Adding multiple scripts to the Scripts to Execute pane
We then repeat the same steps to add a further two scripts: InsertData.sql and SelectData.sql.
We have now opened and created all the scripts in the Scripts to Execute pane that we need for our tasks.
Step 3: Setting up a database distribution list
We now need to set up a database distribution list against which we can execute our scripts.
In the Database Distribution List pane, we must click Configure. We can then view the databases on our SQL Server by expanding the list in the Databases to Add pane.
Clicking the Add button enables us to move the three databases we need to our new database distribution list.
Step 4: Selecting how we want SQL Multi Script to behave in case of error
Before we execute the scripts, we can determine how we want SQL Multi Script to behave if there is an error on execution.
This time, we will choose Stop executing. This means SQL Multi Script will stop executing against all scripts on all databases if there is an error.
We can now execute the scripts. The first script will create the database schema, followed by the second script to populate the databases with our data, and finally a third script to query the database and retrieve data.
Step 5: Executing multiple scripts with a single click
To execute our scripts against our three databases in one go, we click Execute Now.
Step 6: Viewing the results in the Results pane
The results appear in the Results pane, which allows us to browse between the messages and results for different scripts and databases.
Highlighting a separate script in the Scripts Executed pane means we can see the results for just that script.
We click on the SelectData.sql script to view the results.
In this script, we asked to retrieve data from the data with which we just populated our three databases.
Step 7: Viewing the results as text format
We can choose to view the results either as a grid or as text. Depending on our decision here, we can save the results as a .csv or .txt file, which can then be opened in an external application.
These fast and easy steps, from creating a script through to saving the returned results, cut out time-consuming and repetitive administration during script deployment.