Set the following options to control the conversion process.
Select the SQL Server database to contain the converted objects. The default is the database specified by the ODBC data source.
Press the button to create a new SQL Server database.
An SQL Server Transact-SQL compatible script is always generated when tables and/or queries are converted to SQL Server.
Specify Deploy Immediately to immediately compile the generated script or Create Script Only to generate a script file for later deployment.
For more demanding migrations, it may be desirable to break the project down into three or four steps. First, all table definitions are created, compiled and checked for errors. Export the data next. After successfully completing the previous tasks, indexes are now built. Relationships can be created as a final step, allowing some testing to be performed first and any incremental design changes to be implemented.
You can also create all table definitions, indexes, relationships and load data all in one step. In addition, Scriptoria fully supports incremental changes. Select one or more tables to convert and Scriptoria will automatically drop existing tables, load data and reinstate any relationships.
To accommodate these needs, Scriptoria is capable of creating separate table, index, and relationship scripts.
Tables: Select this option to include SQL Server table definitions in the generated script. The table definition will specify:
Data types, as defined in the Data Type Defaults form
NOT NULL when specified or implied by the associated Access field
DEFAULT clauses that can be translated from the associated Access Default property
CHECK clauses that can be translated from the associated Access field and table Validation Rule properties.
Table and Index table space definitions when specified. (see the Assign Storage section)
The following DDL commands may follow the table definition:
Primary and Unique key declarations using the ALTER TABLE construct
Triggers that implement Validation Rules that specify user defined Validation Text.
Indexes: Check this box to include indexes in the generated script. Access indexes are translated into equivalent SQL Server indexes, including the unique property. Access index names are renamed to valid SQL Server names. Although Access allows the creation of duplicate index definitions, Scriptoria ensures index singularity.
Data: Check this box to load MS-Access table data into the SQL Server database.
Relationships: SQL Server foreign keys are generated when MS-Access relations specify the Enforce option. The SQL Server relationship will include the ON CASCADE DELETE and/or ON CASCADE UPDATE option when specified by the associated Access relationship.
Add Timestamps: Timestamps improve update performance and mitigate concurrent user updates. When this box is checked, Scriptoria adds a timestamp field when a MS-Access table contains one or more fields that specify floating point (Single or Double), Memo or OLE Object data types.
Indexes that specify the same fields in the same order as another index that has an equal number or more fields are redundant. For example, if index Inx1 includes fields A and B and Inx2 includes fields A, B, and C; then Inx1 is redundant. Redundant indexes add overhead when inserting and deleting rows and may confuse the SQL Server query optimizer.
You can instruct Scriptoria to either Ignore (the default) or create redundant indexes. Warning messages are issued and written to the Conversion Warnings table when Scriptoria encounters redundant indexes. The message includes the table name, the redundant index name, and the name of the index that covers the superfluous index.
When two indexes include the same number of fields in the same order, primary and unique indexes take precedence over non-unique indexes. When two unique (not primary) indexes include the same number of fields in the same order, one is arbitrarily deemed redundant.
When one or more existing SQL Server tables are selected to be converted, any SQL Server views, procedures and functions that use the tables will become invalid since the SQL Server tables will be dropped before they are recreated.
When the Recompile box is checked, Scriptoria will regenerate the associated MS-Access queries and recompile them. If you do not wish to recompile SQL Server objects after tables are dropped, remove the check from this box. Note that any dependant views, procedures and functions will become invalid and unusable until you use the Query Conversion Facility to recompile them.