SQL Server (MSDE) on Win 95/98 computers

This info is probably a bit out of date now, and the links may not work, but we'll leave it here.

Did you know that you can get full SQL Server 7 functionality on your Win 98/Me* 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.

* I'm informed that MSDE is only supported for W98 and up. We'd be interesting in hearing from any of you diehard W95 users who managed to get it working, though.

Here is a simple graphical interface you can use to send SQL commands to MSDE from an Access database. It's written in Access 97 but should convert easily to its more recent siblings. For more details, see the OSQL interface page.

Where to get MSDE
Getting Help
MSDE Utilities
Creating a database
OSQL Help
Access 97 SQL Upsizer
Creating a table from ACC97
Setting a permanent connection
Creating an ODBC link
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 the On-Line User Manual.

For a good on-line course on T-SQL see Michael Hotek's site.

MSDE Utilities

There are some useful utilities in the directory C:\MSSQL7\BINN which you can access from the DOS prompt.

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

From the MS-DOS prompt type

OSQL /Usa /P

This should give you a "1>" prompt.

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

You can also use OSQL program to find out other details, such as the server name and the current version:

	OSQL /Usa /P

	SELECT @@SERVERNAME
	GO

	SELECT @@VERSION
	GO

	exit

OSQL Utility Help

The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server. For a complete list of commands see the OSQL Utility Help Page.

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

Create a pass-through query as follows:

From the Query tab, click on New > Design View, but Close the Show Table menu without selecting anything.
Use the menu options Query > SQL Specific > Pass-Through

Enter query:

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

For further details on pass-through queries, use Access help on CREATE TABLE and ALTER TABLE.

Alternatively, use our OSQL interface and type in the T-SQL commands directly from an Access project.

To set a connection permanently in a pass-through query

Create a pass-through query as above.
Click on the Properties icon.
Click on the Build (...) button for the Connection, select the DSN, enter the password, and when prompted "Save Password with query" say Yes.

Creating an ODBC link

Once you have created a database with some tables, you can continue to use the OSQL interface to work with the tables, but it's much easier to have an ODBC link from an Access database. Here are the instructions you need from a stand-alone computer:-

From an Access database, select menu options File > Get External Data > Link Tables...
In the box Files of type: select ODBC Databases (it's at the bottom of the list)
Select tab Machine Data Source
Click on New...
Select System Data Source (unless you actually logon to your system and want to limit access only to yourself, in which case select User Data Source). Click on Next
Select a driver: SQL Server (it's at the bottom of the list). If you don't see SQL Server here, it needs to be installed from the Office setup disk.
Click on Finish and a new form will display.
Enter the following:
- Name: test1 - this is the name you will access it by via OBDC. Hint: it helps to use the same name as the SQL Server name
- Description: (optional) - enter whatever you want here.
- Server: (local) - this should be your only option on a stand-alone MSDE system
Then click on Next
In the option "How should SQL verify authentication..." select With SQL Server Authentication
Check Connect to SQL Server and fill in the boxes
- Login ID: sa
- Password: (leave blank)
Note: The login ID will default to your computer name, it is important that you overwrite it here. Click on Next
Check Change the default database and select your database from the list. This list will use the names as created in SQL Server, in our case test1. Leave the other options as they are and click on Next
Click on Finish then Test Data Source - it should respond with TEST COMPLETED SUCCESSFULLY. Click on OK and it will return you to the Select Data Source/Machine Source menu.
Select your database from the list, in our case test1. Click on OK
In the SQL Server Login menu:
- Login ID: sa
- Password: (leave blank)
Click OK
The Link Tables menu will display. This list will include all sorts of SQL system tables like INFORMATION_SCHEMA.CHECK_CONSTRAINTS. Your tables will begin with dbo. (database owner). In our example we select dbo.FirstTable. You may select more than one table here.

Note: check the Save Password box in this menu to save having to enter the login and password each time you use the table in the future. However, if you want subsequent users to have to login, then leave this unchecked.
If you hadn't specified an index field in the SQL table, you will be prompted to select one (but of course you'd never create a table without a unique index, would you?).

That's it. The table should now exist in your Access tables window as an ODBC link, in our case dbo_FirstTable. You can view, enter and edit data in this table just like in any other Access table.

Once you've created the ODBC Machine Data Source in instructions 1 to 12 above, you don't need to create a new data source each time. You can create a link from any other database on your machine by following instructions 1 to 4 then jump to instruction 13.

How to install MSDE

On the CD there is a tedious slide show with sound and vision that eventually tells you how to install it. It's wrong. Or at least it's confusing. Here's what to do.

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.
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.
Using MS-DOS (Start > Programs > MS-DOS Prompt), get into the temp directory
```
cd \temp
```

At the DOS prompt, type

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

Re-boot your system.
Check the log file C:\Windows\setup.log. The system should be installed in directory C:\MSSQL7.

Contact

Email Us

This page last updated 10 January 2009 (format only)

SQL Server (MSDE) on Win 95/98 computers

Contents

Contact