SQL Server and the Deploy URL

The Expense Report application stores the expense report information in a SQL Server 7.0 database. Each instantiation of the application requires the creation of several SQL Server tables for 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 one parameter. This parameter is the location of the database to connect to, which is on the local server where the Expense Report application is being instantiated.

' Create a new SQL Server 7.0 database
Set oSQLServer = Server.CreateObject("SQLDMO.SQLServer")
Call oSQLServer.Connect("(local)")    ' Use NT integrated security (no UID or PWD) 

Note This Deploy URL uses Windows NT integrated security. Also, SQL Server is on the local server.

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 Expense Report Application Physical Directory and is a concatenation of the values held in the variables SourceName and NextInstanceNumber—ExpenseN, 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 (expense_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 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.

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

Once the SQL Server database has been created successfully, the tables can be created within the database.

Create the tables / stored procedures
    Call oDatabase.ExecuteImmediate(SQLText, SQLDMOExec_Default, len(SQLText))  ' Create tables / stored procs

Then the references to the log file, the data file, the database, and SQL Server are destroyed.

Set oLogFile = nothing
Set oDBFileData = nothing
Set oDatabase = nothing
Call oSQLServer.Close()
Set oSQLServer = nothing

To follow the next steps in the script's execution, see Save Configuration Values into Instance Database.