SQL Server Desktop fills a unique position in the database world by offering SQL Server’s power and flexibility with Access’s familiar and easy-to-use development tools. It’s the best of both worlds—an alternative for IT consultants who use Access but need more, without having to actually upgrade to full SQL Server. Once you’ve made the decision to try it, it’s a simple matter to install it and get to work. Before you begin, check out this explanation of the ways to install SQL Server Desktop.

Who has it?
Anyone with Office 2000 Professional (or later) or Access 2000 (or later) has SQL Server Desktop. It will run on Windows 95 and later, Windows Me, Windows 2000 Professional and later, Windows NT 4.0 Workstation, and Windows 2000/NT Server. Keep in mind, however, that SQL Server Desktop security features aren’t supported in Windows 95, Window 98, or Windows Me.

There are two versions of SQL Server Desktop. The first version, Microsoft Data Engine (MSDE 1.0), came with Office 2000 Professional and Access 2000. The current version, SQL Server Desktop (MSDE 2000) comes with Office XP and Access XP.

Installing from the Office CD
If you’re not upgrading from MSDE 1.0 (the first version of SQL Server Desktop), the installation process is easy:

  1. Find the setup.exe file in the MSDE2000 folder on the Office XP CD-ROM.
  2. Double-click the setup file to begin the process. There are no screens or buttons to click, and it will require a few minutes, so don’t worry when nothing seems to happen.
  3. When prompted, restart your system. The task bar will display the SQL Server Service Manager icon.
  4. To open the Service Manager, double-click the SQL Server Service Manager icon. Click the Auto-start option as if you want to launch SQL Server when you start your system instead of manually starting the service. This is the same Service Manager used by the SQL Server 2000.

An alternate method
If you’d rather, you can install from the Windows Start button. First, insert the Office Professional XP CD-ROM. Then, click the Start button, choose Run, and then enter this command:
 
X:\MSDE2000\setup.exe

where X represents the designation of your CD-ROM drive.

Upgrading
Upgrading from MSDE 1.0 to SQL Server Desktop (MSDE 2000) can be a more complicated process. There are two options:

  • You can uninstall the earlier version of MSDE 1.0 and then install SQL Server Desktop. While this method is the simplest choice, it is not always the right choice if you have been working with MSDE 1.0 for a long time and want to maintain your current settings and files.
  • Use the Upgrade option to install SQL Server Desktop, which requires more work than a clean installation, but does maintain your current settings and files. If you’re using MSDE 1.0 and want to keep your settings and files, use this option. Always make a backup of your MSDE 1.0 files before you begin, just in case.

Uninstalling MSDE 1.0
The first option involves a simple uninstall before following the instructions in the “Installing from the Office CD” section. To uninstall MSDE 1.0:

  1. Open the Windows Control Panel.
  2. Select the Add or Remove Programs tool.
  3. Highlight the Microsoft MSDE 1.0 item in the Add or Remove Programs dialog box.
  4. Click the Change/Remove.
  5. These instructions are for Windows XP. Earlier versions may use Add/Remove or just Remove captions.

Using the upgrade option
The alternative to a clean install is a true upgrade from MSDE 1.0 to SQL Server Desktop. To use this installation option, click the Start button on the task bar, choose Run, and enter the following:
 
X:\MSDE2000\setup.exe UPGRADE=1

where X represents the designation of your CD-ROM drive. Using the options in Table A, you can exercise a bit of control over the process. Specifically, you can:

  • Specify destination folders.
  • Name the new SQL Server instance.
  • Set the SQL Server logon security mode.
  • Create a log file for troubleshooting (if necessary later).
  • Add a reinstallation switch.

Table A


Option
Syntax
Explanation
? /? ? displays a list of setup switches.
i /i packagefile
 		i lets you specify a Windows Installer installation package file (an MSI file) where packagefile is the name of the MSI file.*
SETTINGS /SETTINGS inifilename
 		SETTINGS identifies an INI file that specifies more installation options. You won’t use this option with any other command-line options in this list. Instead, list other options in the INI file.
TARGETDIR /TARGETDIR=”drive:\path\“ TARGETDIR identifies where the installer installs the executable files.
DATADIR /DATADIR=”drive:\path\“ DATADIR identifies the folder where the SQL Server system is built.
INSTANCENAME /INSTANCENAME=”INSTANCENAME“ By supplying a unique name, you can maintain the MSDE 1.0 o the same system with SQL Server Desktop. The INSTANCENAME argument must be in all uppercase letters with no space characters.**
L /L* filename
 		L creates an error log named filename. Add a v to the option (L*v) to create a more verbose log.
*You can get more information on package files at the MSDN Web site.
**If you’re running MDAC 2.5, you can’t use the INSTANCENAME option.

You may find the / character before each switch unnecessary. Although Microsoft documentation shows them, I’ve run several of them successfully without the / character.

To use one of the options in Table A, simply tack it onto the Run statement. For instance, to create a log, you’d use the following installation Run command:
 
X:\MSDE2000\Setup.exe /L* C:\DELog.txt

After installing SQL Server Desktop, simply open the new text file, DELog.txt, in a text editor to see any resulting error messages.

Installation complete
Upgrading MSDE 1.0 to SQL Server Desktop requires a bit of planning and thought, but once you decide which options you need, the process itself should be trouble-free. The easiest solution, of course, is a clean install, and the best way to go when possible. If you have any problems upgrading MSDE 1.0 to SQL Server Desktop, see “Configuring SQL Server 2000 SQL Desktop Engine.”