This document is a short user guide for the Microsoft SQL And Access To InterBase Wizard.
Overview
Installation
How to get the Wizard
What to download
Setup Instructions
Using the Wizard
Step 1: Welcome
Step 2: Select Source Database
Step 3: Select InterBase Database
Step 4: Customize Migration
Step 5: Customize Migration
Step 6: Migrate Database
Tutorial: Migrating Northwind
HOWTO: Migrate text files
HOWTO: Migrate only certain records
HOWTO: Schedule migrations
Where to go for support
The Microsoft SQL And Access To InterBase Wizard (Wizard for short, code-named sql2gdb), is a Windows-only application which can aid user migrate databases from Microsoft SQL (MSSQL) and Access (also referred to as Jet).
Because the Wizard uses a generic data-access layer (ActiveX Data Objects, or ADO), it can also migrate databases from other sources, such as Oracle or Paradox. However, the database this Wizard supports explicitly are MSSQL and Jet.
The Wizard has a number of customizable options, and can generate scripts which can be modified freely by the end-user, and saved to be executed at a later time.
Presently, the Wizard is residing at IBPhoenix (http://www.ibphoenix.com). From here, you can download the latest updates on software, documentation and support.
There are three files for downloading, depending on what you already have installed on your system:
To setup the Delphi 5 Packages, uncompress the file using WinRAR or WinZip into a temporary folder, and then copy them to your system folder. If you have Windows 95/98/Me, you should copy them to:
C:\Windows\System
If you have Windows NT/2000, copy them to:
C:\WinNT\System32
To setup the Wizard, uncompress the file into any folder. The Wizard can be run by double-clicking on the sql2gdb.exe icon.
The Wizard will guide you through a number of steps. Each step has a clearly-defined purpose, and help is provided at each step to get you through. The Wizard was designed to be able to run to completion by clicking repeatedly on the Next button on every step except the last, which is completed by a click on the Migrate button.
The Wizard can be run by double-clicking on the sql2gdb.exe icon, which should be in the folder in which you uncompress the sql2gdb.zip file.
This step simply welcomes you to the Wizard, and explains its purpose. If you are connected to the Internet when you run the application, the Wizard will check whether there is an update available. If you wish to trigger this manually (because you want to connect, or because you were not connected when you launched the Wizard), you can do this by clicking on the Firebird picture.
If you have run the Wizard before, and you chose to save your script in the last step, then you can re-load it into the Wizard. Clicking through it will take you every step of the way, and you will not lose any changes.
To load a previously saved script, click on the Browse... button, select your SQL script file, and then click the Load script button.
If you would like to migrate files from plain text files, you might want to read the HOWTO: Migrate text files section.
This step allows you to select a data source. A data source is where the data to be migrated will be taken from.
What you are supposed to fill in here is what is know as a connection string. A connection string specifies which library will provide the information (Jet, MSSQL, ODBC, or other), and any additional information that provider might require to connect. If you are familiar with your provider's connection string syntax, you can just type it in.
Because this is a pain in the neck, there are many other ways to select a data source:
In this step, you enter information about the InterBase database you wish to migrate the data to, or whether you want to redirect the whole thing to a script file.
To create a new database, click on the Browse... button. If you select an existing database, you can either delete it and recreate it (by checking Delete the existing database (if any)., which will not complain if there is no database), or you can migrate data into it (by checking Use existing.).
Either way, you will need to specify a user name and password; the defaults are those declared in the documentation, and there is no need to change them unless you have explicitly changed the password.
If you want to skip all these steps, and just send the migration to a text file you can later run from the command-line, check Yes, I want to migrate the database to a file., and select an SQL script file.
Warning: when directing output to a file, certain data may be lost, such as BLOBs and binary fields.
For simple migrations, there is no need to change anything in this step. You can simply proceed by clicking Next >. The Wizard will generate all scripts, giving feedback about its current activity.
For a large number of migrations, however, you may want to customize the way the Wizard behaves.
Option | Description |
---|---|
Use dialect 3. | If you check this box, the Wizard will use DATE, TIME and TIMESTAMP fields to correctly emulate those found in the source system. It will also enable the use of quotes for naming identifiers. Lastly, it will keep your breath fresh and prevent tooth decay. Unless you need to migrate to InterBase 5.6, there is really no reason why you should uncheck it. |
Migrate autonumeric fields. | If you check this box, the Wizard will create a generator for IDENTITY fields in SQL Server and AutoNumber fields in Jet. It will also create a trigger to assign a new value to the field. |
Skip metadata. | If you check this box, all metadata generation scripts will be skipped. This is useful when you already have an existing database structure, and you want simply to move the information. |
Skip triggers. | If you check this box, then triggers will not be created to auto-assign values from generator into the AutoNumber field. |
Create GUID domain. | If you check this box, a script will be generated to create an InterBase domain which can handle Globally Unique Identifiers. |
Interrupt on errors. | If you check this box, if any errors arise during the migration, a message box will be displayed with the error and migration will stop. Otherwise, migration continues and all errors are shown on a text box after all scripts are executed. |
Name map method | Because the naming constraints for identifiers
are very different in MS SQL, Jet and InterBase,
there is usually an intermediate step, called
name mapping or name mangling,
which assigns an InterBase-friendly name to
each source field and table identifier. There
are currently three methods in which this
assignment takes place:
|
Character Set | If you do not select anything, the default character set, NONE, will be used. You can either type a character set name or select one from the drop-down list to override this. |
Select Tables | If you do not press this button, by default, all tables as reported by the source will be migrated. If you wish to migrate only some of the tables, you can click this button, and then select from a list with check boxes. |
Select UDF Libraries | If you do not press this button, by default, no UDF libraries will be declared. If you click this button, all available UDF libraries with declarations will be presented, and you can choose which declarations to migrate. UDF declarations are the first things declared, so you can use them later on during data pumping. For more information on this feature, see the SQL2GDB UDF Discovery document. |
Select Defaults | This button will present you with a form to enter default values for different fields. The fields are assigned the specified values when the source database has a NULL. The dialog shows how to specify field names. You can load from/save to a text file to keep an external reference (the encoding in the .sql file is rather cryptic). You can also drag a file on the grid to load it. |
In this step, all scripts have already been generated. You can usually continue by clicking Next >, but you might also find it interesting to review the scripts generated.
To change the contents of any script, simply select it and edit its contents. To erase a script, select it and erase all its contents - it will not erase the item from the list, but nothing will be executed.
In this step, you can simply click the Migrate button to execute all scripts. The Wizard will then run all of them, informing you of its progress.
Before or after the migration, you can save the generated scripts to an SQL script file by clicking Save scripts.... The script will also include, in its first lines, a number of attributes, which enable the Wizard to recreate all options and customization which were made. If you save the script file, the wizard will ask you whether you want it to generate additional files, placed in the same directory, to schedule the migration script.
Security Warning: by default, the password you entered is saved along the script file, together with all other attributes, in plain text. Make sure you keep the file safe.
If there were any errors, they will be displayed in a box, and a button will appear, to enable you to save them to a text file and analyze its contents.
Lastly, you can click the Finish button to close the Wizard. Congratulations!
This section of the user guide is still under construction. Thanks for your understanding.
The latest version of the wizard includes a sub-wizard (ugh) to prepare schema.ini files. These files allow the Text ODBC driver to recognize the format in the text files and in turn, allow the wizard to migrate them.
There are two important steps to consider. First, the welcome screen allows you to launch the sub-wizard. Second, when you drag a file in the source selection step, note that the driver will try to read all candidate text files. You will probably want to select only the files with real data to migrate.
Now, on to using the sub-wizard. The first step allows you to select a text file to configure. If you need to migrate more than one file, you can run this wizard repeatedly.
The second step allows you to configure some global properties of the text file, such as the data layout and fields. The fixed-width column layout is quite simple to use - note that, by default, the whole file is selected as a single field.
The third step shows you the fields, with the wizard's guess on what kind of data is there. Currently, the wizard confuses most integers with dates. Anyway, you can pretty much include any kind of information here. You can then click Save to save the schema.ini file, and then click Finish to close this wizard.
One last thing to take into account is that there are some additional properties (like date format) that the sub-wizard simply is not managing at the moment. You can always edit the schema.ini file with a regular text editor, such as Notepad or TextPad. The file format is described in this page.
By default, the wizard will try to migrate all the records in the tables you select. If you wish to migrate only certain records, on the other hand, you will need some trickery:
For example, suppose we have the following schema:
create table employees (
emp_no integer not null primary key,
emp_first_name varchar(64) not null,
emp_last_name varchar(64) not null,
salary numeric(6,2) not null
)
If we want to migrate only employees who earn less than $1,500, we create a table using the QueryDef Wizard in Access, or with the following T-SQL statement in MSSQL:
CREATE VIEW emp_view_move AS
SELECT *
FROM employees
WHERE salary < 1500
Then, when we run the wizard, we need only
select emp_view_move
instead of employees
,
and the desired records will be migrated.
There are numerous scenarios in which scheduling migrations can be useful. For example:
In all the above scenarios, you can create a scheduled task on the computer with Access or MS SQL, using the Task Scheduler service in Windows NT/2000 or the Scheduled Tasks folder in Windows 95/98/Me.
This HOWTO will show you how to create a simple batch file which can get the job done simply and efficiently. Note that as of release 2002-05-02, the Wizard will create skeleton files for you if you save your script.
First, you need to run the migration wizard once. Suppose we
take the dbdemos.mdb file which comes with many Borland
products - you may find this database in C:\Program Files\Common
Files\Borland Shared\Data\dbdemos.mdb
. Select as a destination
database c:\dbdemos.gdb. Save the generated
script to C:\sql.sql.
Notice that if you click Migrate in the last step, you will get two errors. They can be corrected in the generated script, but because we are migrating from a database we know to be consistent, they an be removed from our checks. This is typically done when you will not be updating the database.
To remove the useless constraints, open C:\sql.sql with Notepad or your favorite text editor, and remove the last entries, beginning with /* KEY ...*/ comments.
Now, create a batch file, again using Notepad if you will. Name this file C:\bat.bat. Assuming that you have installed the wizard in C:\Projs\sql2gdb, write the following into the batch file:
@echo off
rem Migrate the database
echo Migrating database....
c:\Projs\sql2gdb\sql2gdb.exe c:\sql.sql
rem Zip (or RAR, if you will) the output file
echo Compressing file....
SET PATH=%PATH%;C:\Program Files\WinRAR
rar a dbdemos.rar dbdemos.gdb
rem Remove the following line if this will be scheduled.
pause
After you have finished this, you can use a VBScript file, for example, to automate Outlook and send you the database by email. If you need help doing this, contact me at marcelo.lopezruiz@xlnet.com.ar. There is an article I will publish soon in my homepage; you might want to bookmark it and check it about once a month.
There is no formal support for this tool. However, you can e-mail any of the developers who have given me permission to publish their addresses, found in readme.htm file, or you can submit your questions on the IBConversions e-group (the homepage is here).