Another nice feature of version control systems is their ability
In VSS, this API is actually a COM interface that you can access
from any Automation-capable programming environment such as Visual
Basic or Delphi. Using the VSS Automation interface, you can do
basically anything the VSS Explorer can do because it uses this
interface itself. Via
As an example of how powerful and easy this is, I've included a
Delphi-based utility that can scan a VSS project tree for SQL
scripts and extract them to a pair of T-SQL script files that you
can then run. GGSQLBuilder finds each SQL script in a VSS project
GGSQLBuilder can be used interactively as well as from the
command line. When run interactively, it takes the form of a
wizard: It prompts you for the data it needs to locate your
scripts, you choose some output filenames, and it finds and
I mentioned that GGSQLBuilder will extract your SQL scripts to a
pair of T-SQL script files. Why two? Because GGSQLBuilder is
designed to generate scripts for an end-
Typically, end-user applications based on SQL Server make use of two types of databases: one or more user databases and the master database. As you might expect, user databases usually store end-user data and application-specific T-SQL code objects such as stored procedures and views. The master database, on the other hand, is typically used to store custom system procedures and functions that are either system-oriented routines or that contain code that needs to be shared by all databases. As a rule, you shouldn't store anything in the master database that doesn't meet these criteria. GGSQLBuilder is designed to make extracting these two types of script files painless. Once it has generated the two files, you can run the end-user script against as many user databases as necessary. For example, if you planned to use the end-user script as part of a software update, your update program could call OSQL and use its -d parameter to specify the database to run the script against. You could easily automate applying the script to multiple end-user databases by calling OSQL repetitively from a command file.
As for the master script file, you'd typically run it just once during the installation or update of your software. It would install or update new system objects in the master database that would then be usable across the system.
Why would you want to use a tool like GGSQLBuilder in the first
place? Why not just ship the individual scripts
How does GGSQLBuilder know what to consider a SQL script and to which file to extract it? It makes some basic assumptions about the organization of your VSS projects:
It assumes that the scripts for a
given project reside in
It assumes that any scripts that need to be run against the master database will be located under a subproject named MasterDB.
It identifies SQL scripts in
your VSS project tree by examining their extensions. By default,
files with extensions matching those in Table 4-5 are
Notice that the folders in Table 4-4 are listed in order of object dependence. Objects in the master database may need to exist before the creation of user objects, default objects will need to be created before tables that reference them can be created, tables need to be created before scripts that alter them can be executed, and so forth. GGSQLBuilder will write the scripts it finds in your VSS database to the output scripts using the folder order from Table 4-4.
|MasterDB||Scripts to be applied against the master database|
|Defaults||Default objects (e.g., CREATE DEFAULT)|
|Rules||Rule objects (e.g., CREATE RULE)|
|Tables||Tables (e.g., CREATE TABLE)|
|TableAlters||Table alterations (e.g., ALTER TABLE)|
|Triggers||Triggers (e.g., CREATE TRIGGER)|
|UDFs||UDFs (e.g., CREATE FUNCTION)|
|Views||View objects (e.g., CREATE VIEW)|
|StoredProcs||Stored procedures (e.g., CREATE PROC or ALTER PROC)|
|SQL||General SQL scripts|
|UDT||User-defined data types|
The best way to learn about GGSQLBuilder is to run it interactively. If you have a VSS database with some SQL scripts checked into it, feel free to run GGSQLBuilder and allow it to attempt to locate those SQL scripts. If the project hierarchy is deep, you'll notice a delay while GGSQLBuilder examines every version of every file in the project tree. If you've grouped your script project folders (from Table 4-4) together under a single parent project folder (a good practice), you can instruct GGSQLBuilder to start its search with this project. Once GGSQLBuilder has found your scripts, allow it to build the two output script files to see how it works.
Because GGSQLBuilder identifies SQL scripts based on file
extension alone, it's possible that the output scripts it generates
will contain object creation/destruction that you may not want. In
other words, if you have a
into VSS and GGSQLBuilder finds it, you may end up with not only an
unwanted CREATE TABLE statement, but also a DROP TABLE statement if
the original script
Experiment with GGSQLBuilder and see whether it might help in developing updates to SQL Server-based applications and in automating script generation and testing. Keep one thing in mind, though: Tools like GGSQLBuilder won't be of any use to you unless your T-SQL code is stored in a source code management system.