MSDE for
Visual Studio 6.0 comes packaged in an InstallShield command line
installation program. This command line program is intended to ease
the distribution of
MSDE with
desktop and shared database solutions. Although the setup is
designed to run unattended and in a silent mode, there are some
parameters which can be set during installation. In the following
section, we describe two methods for using the
MSDE
installation program. The first uses the
MSDE setup in
conjunction with the Visual Studio 6.0 Package and Deployment
Wizard. The second example creates a custom Visual Basic application
capable of controlling an
MSDE setup.
Q: How do I distribute
MSDE with the
Package and Deployment Wizard? A: Including
MSDE with an
application deployment requires executing a command line statement.
The Visual Studio 6.0 Package and Deployment wizard does not support
executing command line statements. However, Visual Studio 6.0 ships
the Visual Basic source code for the Package and Deployment Wizard.
The project is located in "Program Files\Microsoft Visual
Studio\VB98\Wizards\Template." Developers may modify this code to
include the execution of the
MSDE for
Visual Studio setup.
Q: How do I automate
MSDE
installation using Visual Basic? A: The following code excerpts are taken from the Visual
Basic MSDE
installation sample project. The code below demonstrates how to
customize these setup parameters and deploy
MSDE directly
from a custom Visual Basic application.
Private Sub cmdInstall_Click()
'This function calls a routine to install SQL Server
...
First, control the location of the installation directories.
Here, the “CheckForDrive” function verifies a directories existence.
Note: drvServer, drvData, and drvCD are all Drive List Box
controls:
' First check to see if we need to create the folders
' Server
Succeed = CheckForDir(drvServer.Drive, txtServerDir.Text)
' check for error return
If Succeed = False Then Exit Sub
' Data
Succeed = CheckForDir(drvData.Drive, txtDataDir.Text)
' check for error return
If Succeed = False Then Exit Sub
' If everything is OK we can go on
Here, the InstallShield response file (.iss ) is customized for
the current installation. Note: "Setup.iss" is a renamed
version of the "unattend.iss" file that is distributed with
MSDE. This
file contains the settings used by the command line installation
program:
' Update the setup.iss file with the new directories for
' the server and the data
Notice how the hard-coded directory locations in the file are
replaced:
' Build the value for the directory for the server files
Dir = drvServer.Drive & txtServerDir.Text
Call WriteIni("SetupTypeSQL-0", "SzDir", Dir)
' Build the value for the directory for the data files
DataDir = drvData.Drive & txtDataDir.Text
Call WriteIni("SetupTypeSQL-0", "SzDataDir", DataDir)
Another option is be to skip this step and predefine a custom
response file to be used for all installations. This strategy would,
however, limit all installations to the same data and server
directory locations, and is not advisable.
This code controls where the installation will find the custom .iss
file:
' We need to get the current directory to pass in the
' setup.iss file
Mypath = App.Path
' following line launches SQL Setup using the modified
' setup.iss file as input. For demo purposes, it runs
' SQL Server setup from a distribution CD
' set up the command line to use the CD drive indicated
' from the form and the setup.iss file from the current
' directory
' The command line for a CD drive at D should be:
' start /wait D:\x86\setup\setupsql.exe -f1 setup.iss -SMS -s
Q: How do I distribute database files with
MSDE
solutions? A: Microsoft SQL Server 7.0 and
MSDE use the
same database file formats. These files can be detached from one
database server and attached to another. Detaching database files
provides an easy way to distribute databases with
MSDE desktop
and shared solutions. For example, a developer can design a database
using SQL Server 7.0 Developer edition and then detach the database
and distributed it with an
MSDE
solution. The database can then be attached to
MSDE.
The following code excerpts are taken from the Microsoft Visual
Basic 6.0 MSDE
installation sample project
. The code below demonstrates how a database can be detached from
the server, and then attached again.
Database files must be detached before they can be attached to
another server:
Public Function DetachDatabase(DBName As String) As Boolean
' This function detaches a SQL Server or MSDE database.
This sample uses late binding. Early binding will work as well:
Dim oSvroot As Object
Set oSvroot = CreateObject("SQLDMO.SQLServer")
…
First, make a connection to the database server:
' Connect.
oSvroot.Connect "(local)", "sa", ""
The actual database detach is fairly simple when using SQL-DMO.
“Dbname” is the name of the database that will be detached and
“True” tells the server to skip the update of statistics before
detaching (updating statistics provides the database server
information about the size and contents of a database):
' Detach
Call oSvroot.DetachDB(DBName, True)
…
Once database files are detached, they must be attached:
Public Function AttachDatabase(DBName As String, _
FileName1 As String, Filename2 As String) As Boolean
' This function attaches a database file to an SQL
' Server or MSDE server.
…
Again, the first step is making a connection to the database
server:
' Connect.
oSvroot.Connect "(local)", "sa", ""
…
There are two database files to attach. One file contains the
data (.mdf) and one contains a transaction log (.ldf). Here we pass
in the path to each of these files separately:
