In the Generating SQL Scripts section, I mentioned that you can build lists of tables, views, and stored procedures dynamically. To do so, you can take advantage of the same concept that underlies SQL command execution. For example, each table definition starts with the string CREATE TABLE. As the code at callout B reads the WhsDBStructures.sql file, it can look for this string, then parse the table's name from the line. Parsing the name lets you replace the manually maintained lists of database object names that populate the code at the beginning of Listing 4.
Callout C in Listing 5 shows the VBScript code's final section, which inserts the static data into the database. I have two caveats for this code. Note that the OpenTextFile command's fourth parameter carries a potential gotcha: The DTS wizard that creates data files defaults to ANSI text instead of Unicode. To support ASCII files, you need to set this parameter to its default of 0. The next item of interest at callout C is the SET IDENTITY_INSERT command, which executes before the script begins reading data. This command lets you insert explicit values into a table's IDENTITY column. Turning off the IDENTITY column during static data insertion lets the script copy the exported data and maintain the same identifiers. You can find more information about the SET IDENTITY_INSERT command in SQL Server Books Online (BOL).
The code at callout C uses the same basic logic loop it uses to read each line from the data file, except that each data line contains the full set of data values that an INSERT statement can use. As I previously noted, you prepare the data by enclosing the static text in single quotes, thereby generating the file. For the insert to be successful, the resulting lines require no manipulation. You can enhance these files by adding the column headers to the exported data file, modifying the script to read the data file's first line, then using this line and the resulting line for the INSERT statement's column names.
Taking It from Here
After you download and extract the .msi file and scripts, you can customize both the .msi and .vbs files. Modifying the .msi file requires you to use third-party tools. You can also modify the installation's script files without recompiling or otherwise modifying the .msi file that executes them, as long as you use the same filenames. Additionally, when you run a Windows Installer package, you automatically generate an uninstall script. For this package, the uninstall script removes all the package's .sql and .vbs script files; however, the database that you created remains untouched so that you don't lose data.
After you run the installation package and examine both the newly created database and the .sql files, you can use and customize the tools and concepts I covered in this article to create and install SQL databases on the fly in a consistent and repeatable manner. You can enhance the installation's basic package and scripts over time by adding
- a check to validate a database connection before opening the script files
- a log file to record each command's success or failure
- a script to dynamically build the list of objects that require a GRANT statement to assign permissions
- a script to dynamically create additional roles and users
If you need more information about scripting and methods of manipulating the .vbs files in the installation package, see the Windows Scripting Solutions newsletter. At the newsletter's Web site (http://www.winscriptingsolutions.com), you'll find tips and tricks about mastering scripts and using them to automate your administration tasks.