Learning SQL Multi Script - 1.1

Worked example

This worked example demonstrates how to use SQL Multi Script to create and query three databases.

In this example, the Magic Ratchet Company is deploying a SQL Server database to run on their live Web server. In addition to the live database, the company want two identical copies of the live database that they will use for development and testing.

The process has several parts:

• create scripts for creating, populating, and querying the databases
• create the databases
• create the schema and populate the databases
• confirm that the deployment was successful, by looking at the results

You can follow the example on your own system. You will need access to a SQL Server. Note that this example uses SQL Server 2005; if you are using SQL Server 2000, you can follow the example, but you will see slightly different results.

Creating the scripts

The first step is to add the scripts for this example to the list of scripts that SQL Multi Script can execute. We are going to do this by creating new scripts in SQL Multi Script.

1. If you have not yet started SQL Multi Script, select it from your Start menu.

   

2. If you have started SQL Multi Script for the first time, the Scripts to Execute pane contains only script1.sql, which is an empty script that SQL Multi Script creates when it first starts. If you do not have this script in your list, click New to create it.
3. Ensure that script1.sql is selected, then click here to open the first script.
4. Copy the script and paste it into the Script pane in SQL Multi Script.
5. Right-click on the new script in the list, then select Save As, and save the script as SMS_CreateDatabases.sql
6. Repeat the previous steps (from step 2) to create three more scripts and save them. The scripts you need to create are:
   • SMS_CreateSchema.sql - click here for the script to copy
   • SMS_InsertData.sql - click here for the script to copy
   • SMS_SelectData.sql - click here for the script to copy

     You now have all the scripts you need in the Scripts to Execute pane.

     

Creating the databases

The worked example uses the following databases:

• RatchetDev
• RatchetTest
• RatchetLive

We are going to create these by executing the SMS_CreateDatabases.sql script that you have already added to SQL Multi Script.

1. You need to specify the database that SQL Multi Script will execute the database creation script against. In the Database Distribution List pane, click Configure to open the Configure Database Distribution Lists dialog.
2. For this example, you will be executing the script against the default database on a SQL Server. Select the SQL Server instance on which you want to create the databases, then click Add. SQL Multi Script adds the default database for the SQL Server (e.g. master) to the Databases to Execute Against list.

   

3. Click OK. The database you added is now shown in the Database Distribution List pane on the main screen.
4. Before you execute scripts, you need to choose how SQL Multi Script will behave if there is an error executing any part of one of the scripts. You do this in the On error list above the Database Distribution List pane.

   For this example, select Stop executing on database with error.

   

   For information about each of the options here, click .

5. In the Scripts to Execute list, right-click SMS_CreateDatabases.sql then select Execute This Script Only. Note that this option executes only the script you have currently selected, so there is no need to exclude the other scripts manually before you do this.

   SQL Multi Script executes the SMS_CreateDatabases.sql script and displays the Results pane.

   

In this example, the script does not contain statements that return results, so the Results pane only includes a Messages tab that reports that the script has been executed successfully. In the next step, we will see an example that returns results.

Creating the schema and populating the databases

Now that we have created the three Ratchet databases, we need to run scripts to create the database schema and populate the databases. To do this, we are going to set up a distribution list that includes these databases, then execute scripts against all the databases at once.

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

   

2. The dialog opens with the default distribution list displayed. You are going to create a new distribution list just for the Ratchet databases. Click New to display the Create New Database Distribution List dialog. Type Ratchet in the Name box, then click Create.

   The new Ratchet distribution list is now shown on the dialog.

3. To add databases to this distribution list, find the SQL Server on which you created the Ratchet Databases, then click to see all the databases in this SQL Server. Select the RatchetDev, RatchetTest, and RatchetLive databases, then click Add.

   

4. Click OK to save your new database distribution list.

   The Database Distribution List pane displays your new list:

   

5. You now need to select and order the scripts to execute. In the Scripts to Execute list, the check boxes next to the scripts you created are currently all selected, which means that all the scripts will be executed. You have already executed SMS_CreateDatabases.sql, so clear the check box next to it to exclude it.

   Notice that the numbers next to the scripts change, to indicate the order in which the remaining two scripts will be executed. You need to execute the scripts in the following order:

   • SMS_CreateSchema.sql
   • SMS_InsertData.sql
   • SMS_SelectData.sql

     If your scripts are not listed in this order, re-order them using and .

     Your script list should now look like this:

     

6. To execute your scripts, click Execute Now.

   SQL Multi Script executes your scripts and adds a Results pane at the bottom of the window.

Confirming that deployment was successful

We have now created and populated the tables for the three Ratchet databases. The scripts we executed also queried the databases to ensure that deployment was successful.

1. In the Scripts Executed list, click on SMS_SelectData.sql. The results for this script are displayed.

   

2. Notice that there are two Results tabs for this script: one for each Select statement that the script contained. For more information about the Results pane display and how to save your results, see Viewing results.

You have now completed the worked example. This has provided a basic overview of how you might like to use SQL Multi Script, but there are features that we have not covered here. You could discover these features by experimenting further with the Ratchet databases, and by clicking on to view hints on using SQL Multi Script.