SQL Server on Win98

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.

I've devised 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.

Contents

• 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

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

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 VARCHAR(20), LastName VARCHAR(30), _
     SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY);
   

4. 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

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.

Creating an ODBC link

1. From an Access database, select menu options File > Get External Data > Link Tables...
2. In the box Files of type: select ODBC Databases (it's at the bottom of the list)
3. Select tab Machine Data Source
4. Click on New...
5. 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
6. 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.
7. Click on Finish and a new form will display.
8. 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
9. In the option "How should SQL verify authentication..." select With SQL Server Authentication
10. 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
11. 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
12. 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.
13. Select your database from the list, in our case test1. Click on OK
14. In the SQL Server Login menu:
    • Login ID: sa
    • Password: (leave blank)
    Click OK
15. 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.

16. 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?).

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

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.

Feedback or questions: Contact DI Management.    Return to Tips and Tutorials Page.