May 04, 2005

MSDE Install and Executing SQL with osql

MSDE is the free database environment distributed by Microsoft. It can also be used as a networked database in a distributed environment, such as VA2. We have production customers using MSDE for these purposes, so it has been proven to work.

MSDE has essentially the same code base as MSSQL Server, so it's plenty fast enough for VA2. The only thing that isn't great, the install process isn't as easy as installing MSSQL Server, and there isn't a GUI for executing SQL statements and managing the databases. This article gives instructions for installing MSDE and executing SQL statments, including VA2 install instructions.

These instructions cover steps necessary to install MSDE and configure the VA2 schema by executing MSDE sql statements with the osql utility. Without a great any GUI for executing SQL statements, users are stuck with command line options. However, these instructions are easy enough, and within 30 minutes you should have a free database environment for VA2! The instructions also include allowing network access to your MSDE database, so other computers can access your VA2 repository via ODBC. And, authentication is set to allow SQL authentication, not just windows. That is covered in step 3. Enjoy.

MSDE install:

0. Make sure to log in as administrator
1. Download MSDE at http://www.microsoft.com/downloads/details.aspx?FamilyId=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
2. Run the executible, will unzip into temp dir

3. From the temp directory, before running the setup.exe, change the setup.ini to include the following options.
[Options]
SAPWD="SA"
SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=0

Note that the password you set for SAPWD option, will be the password for you SA user to access VA2 repository. So it would be SA/SA you set up in your vconfig.txt file.

4. May require restart

5. Make sure you have downloaded VA2. In the unzipped directory, the locate the \dbinstall\mssql\schmVadmin21.sql file, that will be used in the next step.

6. MSDE does not come with a SQL user interface, like QueryAnalyzer. Which is too bad, MSSQL QueryAnalyzer
is a usefull and good tool.

We need to run the schema creation statement on the VA2 database.
quickest and dirtiest way to do this is to use osql. Here is the bare minimum commands (substitue the path to your schmVadmin21.sql as necessary):

a. open up a cmd
b. to the following command:
osql -E -i "c:\vadmin2temp\dbinstall\mssql\schmVadmin21.sql"

A lot of output will be displayed, if no errors are shown you now have your MSDE environment.

See this article for all the OSQL commands:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003#2

Posted by choppen5 at 12:04 PM