Using MS SQL Databases

  Contents of Using MS SQL Databases
 
  What is MS SQL?
Microsoft SQL Server is a high performance  database which can be used in place of Access databases for your websites.

MS SQL Server databases can be used in your ASP/Perl/iHTML scripts through the use of an ODBC datasource.

Note: You will need a good understanding of MSSQL in order to use an MSSQL database on your website. Although NT-Webspace provides the service, we do not provide consultancy on setting up your database free of charge. 
  Using Enterprise Manager to Manage Your Database
You can connect to the NT-Webspace SQL Server though SQL Server Enterprise Manager which is included with MS SQL 7.

Open the Enterprise Manager on your local computer and select New SQL Server Registration. Click OK to go on to the next page.

You will be presented with a screen similar to the following:

Enter the IP Address of the SQL Server in the Server box as this will be the server we are going to connect to. Switch the connection method to Use SQL Server Authentication and enter the username/password you set on your SQL Server database in the NT-Webspace Control Panel. It is left optional to yourself whether or not you have Always Prompt For Login Name and Password enabled. Next click OK.

If you receive an error (for example "Specified SQL Server Not Found") then you will need to switch the protocol you computer is using to connect to the SQL Server. Ensure this mode is set to TCP/IP - this can be changed using the Client Network Utility.

If a successful connection is made, you should be to expand all the databases installed on the SQL Server. The only database you will be able to access is the one you set up. To add a new table to the database, expand the Console Root to display your database and right click Tables.

You will then be asked to specify a name for the new table. After entering the name, you will be put into the design mode of the table.

 

  Setting up an ODBC Datasource for use with MS SQL databases
Before you can transfer your existing Access database to MS SQL, you will need to Upsize it. This article describes how to set up an ODBC datasource on your local machine to communicate with the MS SQL Server.

Open the ODBC Datasources applet in the Control Panel on your local machine. Change the tab to System DSN and click Add. Select the SQL Server driver type and click Finish.

The first page of the wizard that appears after you click Finish will ask you to specify identification information about your datasource and the SQL server you wish to connect to. Choose a name and a description for your datasource. Enter the IP address of the SQL Server in the "Which SQL Server do you want to connect to?" box. Click Next.

Switch the authentication method to "SQL Server Authentication" and check the box "Connect to SQL Server to obtain default settings for the additional configuration options". Enter the Login ID and password to the ones you have set-up when you added the SQL Server database in the NT-Webspace Control Panel. Click Client Configuration to bring up the options for connecting to the SQL Server.

Change the networking mode to TCP/IP and enter the IP address of the SQL Server in the Computer Name box. Ensure port 1433 is entered in the Port Number box. Click OK to close this window.

Change the default database to your SQL Server database which should appear in the list. Click Next, and then click Finish.

Finally, you can click Test Data Source to verify you can connect to the SQL Server. If all tests pass you will get a Tests Completed Successfully message. If you don't then you may of mis-configured one of the settings
  Upsizing an Access 2000 database
After you have designed your database in Access 2000, initiate the Upsizing Wizard by selecting it from the Tools/Database Utilities menu in Access.

Select the Use Existing Database option from the wizard as we will be using a pre-defined ODBC datasource to connect to the SQL Server.

Switch to the Machine Data Source tab and select the ODBC datasource which you have pre-defined to connect to the SQL Server and click OK to continue.

Enter the username and password you set up on your SQL Server database and click OK to continue.

Now select the tables you wish to export to the SQL Server database and click Next to continue.

The last few steps in the wizard can be set to your own preference although it is recommended to leave them as the default settings. Access will transfer your database to the SQL Server and finally give a status report after the operation. Upsizing has been completed.