Using MS SQL databases HOWTO
Copyright 2001
Version 1.0
This HOWTO describes how to connect to a MS SQL database and how to transfer data
to an SQL server database.
Table of Contents
Connecting to Manage Your MS SQL Database ... 1
Transferring data to an SQL Server database... 4
Connecting to Manage Your MS SQL Database
You will need to use a utility such as Enterprise Manager to upload the current database from your local PC or server to your MS SQL database on our servers. The Enterprise Manager software comes with MS SQL 2000 or MS SQL 7.0 upon purchase of the product.
Shown below is Microsoft Enterprise Manager. The steps show the Add Server wizard.
Start your Enterprise Manager Console, and branch out to the Server group level. At this point you can choose the action menu at the top, and select “New SQL Server Registration” or right click on your server group and choose the same option.
In the box called “Available servers” simply type in the following:
mssql.megasqlservers.com Click “Add.”
That should put that new server name into the “Added Servers” box. Click “Next.”
At this stage, choose the 2nd authentication mode: “The SQL Server Login information that was assigned to be by the system administrator.”
Click “Next.”
At this point, the wizard will prompt you to add the newly registered server to your server group. Select the option labeled, “Add the SQL Server(s) to an existing SQL Server group.”
Click “Next.”
At this point, the server registration is completed as shown.
Click “Finish.”
Transferring data to an SQL Server database
If you have data you want to transfer to your new database, the best way to do this is by using the "Import and Export Data" program, found in the Microsoft SQL Server program group. This runs the Microsoft Data Transformation Services (DTS) Wizard, which will guide you step-by-step through transferring your data to your SQL Server database. If you do not see this option available, you may need to re-install the SQL Server client on your machine, making sure you select "Management Tools" under the options during setup.
While the DTS Wizard will transfer data from a large number of formats to SQL Server, we recommend that you get your data in the format you want it in either an Access database or a local SQL Server database before transferring it to your SQL Server database. This will help ensure that the resulting database is in the format you expect.
A NOTE ABOUT THE MICROSOFT ACCESS UPSIZING WIZARD: the upsizing wizard has a number of known compatibility and scalability issues that make it unreliable when transferring data over a slow (i.e. dial-up) connection, or transferring large amounts of data, among other scenarios. The DTS Wizard is as easy (if not easier) to use as the upsizing wizard, and works very well with SQL Server 7.0 + over a remote connection. Therefore, we do offer technical support for problems transferring data with the DTS Wizard, but not for transferring data with the Access upsizing wizard. If you are having problems transferring data using the upsizing wizard, we strongly recommend using the DTS Wizard.
Follow these steps to transfer data using the DTS Wizard:
1) Verify that you able to successfully connect to you SQL Server database by performing the steps in the FAQ section
2) 2) Run the "Import and Export Data" program, located in the Microsoft SQL Server program group.
The options you will have to fill in will vary depending on the data source type you select in the "Source" drop-down list box. For example, you'd select the default "Microsoft OLE DB Provider for SQL Server" if you were transferring data from a local SQL Server.
4) Click "Next" to continue.
5) On the "Choose a Destination" screen, leave the default "Microsoft OLE DB Provider for SQL Server" as the source. Select "mssql.megasqlservers.com" (or enter it into the box provided) for the Server to copy data to. Select the "Use SQL Server authentication" connection option, and enter your SQL Server username and password supplied to you by your sales rep. Then click the "Refresh" button to retrieve a list of databases from the SQL Server, and select your database from the "Database" drop-down list box. Your database name will be:
6) Click "Next" to continue.
7) On the "Specify Table Copy or Query" screen, select "Copy table(s) from the source database" and click "Next" to continue.
8) If you see the "Select Source Tables" screen, you can specify which tables you would like to copy to your SQL Server. Unless you are an expert user, leave the defaults for the "Destination Table" and "Transform" columns. Click "Next" to continue.
9) On the "Save, Schedule, and Replicate Package" screen, leave the default "Run Immediately" selection checked, and click "Next" to continue.
