SQL Server on Win95/98/Me/Win2000Pro

Did you know that you can get full SQL Server 7 functionality on your Win 95/98/Me/W2KPro PC for free? You don't have to go to the expense of installing an NT server and buying the full version of SQL Server.

Microsoft has a package called MSDE (Microsoft Data Engine). It has full SQL Server 7 functionality with some limitations - there is a 2 GB size limit on databases and a limit of about 5 users.

The catch is there is virtually no documentation and you don't get the fancy GUI database manager programs that come with the NT version. However, it's a great tool for learning SQL at home, testing your database before running it on SQL Server proper, or setting up a modest database for a small office network.

This page has some hints on how to install it and use it.

Contents

• Where to get MSDE
  
• Getting Help
  
• MSDE Utilities
  
• Creating a database
  
• OSQL Help
  
• Creating an ODBC link
  
• Access 97 SQL Upsizer
  
• Creating a table from ACC97
  
• Setting a permanent connection
  
• How to Install MSDE
  

Where to get MSDE

MSDE comes with Office 2000 (Developer Edition only, I believe) or you can download it - all 20 MB of it - from Microsoft if you have a registered version of Visual Studio 6. There is also a CD-ROM called MSDE for Visual Studio 6.0 that's available just for the shipping costs.

Getting Help

Check out Microsoft's page for MSDE (Microsoft Data Engine) especially the Technical FAQ. This has links to an online Technical FAQ.

MSDE Utilities

Setupsql.exe - will set up SQL Server on your machine. Run once. It will ask you if you want to upgrade to Developer Edition. Say yes.

Sqlmgr.exe - can be set so that the SQL server will always run on start up.

ConfgSvr.exe - use this to let your server listen on different protocols.

Osql.exe - DOS version of SQL Server Service Manager. It's all you get, but it's free.

Bcd.exe - use for loading large text files into SQL Server. See Access upgrade book.

Creating a database

OSQL /Usa /P

1>	use master
2>	go
1>	create database test1
2>	go
1>	exit

	OSQL /Usa /P

	SELECT @@SERVERNAME
	GO

	SELECT @@VERSION
	GO

	exit

OSQL Utility Help

Creating an ODBC link

Access 97 SQL Upsizing Tools

There is a free add-in for Access 97 that lets you upsize your Access 97 database to SQL Server. It's not perfect, but it's a start. It comes with a SQL Server Browser, it's OK, but can leave your Access window unclosable.

Download it from Q176614.

There is a very helpful white paper "Upsizing Microsoft Access Applications to Microsoft SQL Server" available at Q175619 that contains a lot of useful advice both on upsizing your Access application and SQL Server in general.

Creating a Table from Access 97

1. From the Query tab, click on New > Design View, but Close the Show Table menu without selecting anything.

2. Use the menu options Query > SQL Specific > Pass-Through

3. Enter query:

   CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT, _
     SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY);
   

4. For further details, use Access help on CREATE TABLE and ALTER TABLE.

To set a connection permanently in a pass-through query

1. Create a pass-through query as above.

2. Click on the Properties icon.

3. Click on the Build (...) button for the Connection, select the DSN, enter the password, and when prompted "Save Password with query" say Yes.

How to install MSDE

1. Copy the two files msdex86.exe and unattend.iss from the \MSDE folder of the CD-ROM to a temp directory on your hard drive, say, C:\temp.

2. If necessary, use a text editor to edit the unattend.iss file and change the destination drive for the SQL software. The default directory is C:\MSSQL7.

3. Using MS-DOS (Start > Programs > MS-DOS Prompt), get into the temp directory

   cd \temp
   

4. At the DOS prompt, type

   msdex86.exe -a -f1 "c:\temp\unattend.iss"
   

5. Re-boot your system.

6. Check the log file C:\Windows\setup.log. The system should be installed in directory C:\MSSQL7.