SQL Server and the Instantiation URL

The document review application stores reviewer comments in a SQL Server 7.0 database. Each instantiation of the application requires the creation of several SQL Server tables in each Team Workspace.

This Instantiation URL script uses SQL Distributed Management Objects (SQL-DMO) to manipulate SQL Server programmatically. For information about programming to the SQL-DMO object model, see "SQL-DMO" under "Building SQL Server Applications" in the Microsoft Platform SDK.

The script begins by using the ASP Server object to create a reference to SQL-DMO SQLServer object. This is the root object of the SQL-DMO object tree. The script calls the Connect method and passes in three parameters:

• The location of the database to which to connect, which is the local server on which the document review application is being instantiated.
• The SQL Server logon name, which is the default "sa" used by SQL Server.
• The password, which is left blank, authenticating the SQL Server logon name.

' Create a new SQL Server 7.0 database
Set oSQLServer = Server.CreateObject("SQLDMO.SQLServer")
Call oSQLServer.Connect("(local)", "sa", "")            '   

Next, the script uses three separate calls to the ASP Server object to create instances of the SQL-DMO Database, DBFile, and LogFile. The script names the database using the Database object's Name property, assigning it the value held in DatabaseName. This value was assigned in Creating the Document Review Application Physical Directory and is a concatenation of the values held in the variables SourceName and NextInstanceNumber—Revw_AppN, where N is the instance number.

The script then uses the oDBFileData object reference to set the properties of the database file. This script indicates the name of the data file, its physical file name (Revw_AppN_data.mdf), and the fact that it is the primary file. It then uses a constant, SQLDMOGrowth_MB, which was declared at the beginning of the script at the same time as the variables and set to the value 0, to indicate that the database's growth increment will be evaluated in megabytes. The FileGrowth property is then set to allow for the database to grow at 1-MB increments.

Set oDatabase = Server.CreateObject("SQLDMO.Database")
Set oDBFileData = Server.CreateObject("SQLDMO.DBFile")
Set oLogFile = Server.CreateObject("SQLDMO.LogFile")
oDatabase.Name = DatabaseName
oDBFileData.Name = DatabaseName & "_data"
oDBFileData.PhysicalName = DataPath & "\" & DatabaseName & "_data.mdf"
oDBFileData.PrimaryFile = True
oDBFileData.FileGrowthType = SQLDMOGrowth_MB
oDBFileData.FileGrowth = 1

The script now creates the primary file group and calls the Add method to append the value held in oDBFileData to the FileGroups collection.

oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData

The script creates, names, and adds the log file to the database. After the log and data files have been created, the new database is added to the Databases collection, and the references to the log file, the data file, the database, and SQL Server are destroyed.

oLogFile.Name = DatabaseName & "_log"
oLogFile.PhysicalName =  DataPath & "\" & DatabaseName & "_log.ldf"
oDatabase.TransactionLog.LogFiles.Add oLogFile
oSQLServer.Databases.Add oDatabase
Set oLogFile = nothing
Set oDBFileData = nothing
Set oDatabase = nothing
Set oSQLServer = nothing

To follow the next steps in the script's execution, see Setting the XML Team Application Values.