SQL Server Upsizing Wizard: Step 8 – Set Upsizing Options

Article
10/23/2006

In Step 8, you can control how the SQL Server Upsizing Wizard exports your tables. You can also specify the changes you want the SQL Server Upsizing Wizard to make to the local database. You can create upsizing reports, redirect views to use remote data, create new remote views on tables that are upsized, and save passwords with views.

Specifying Table Attributes to Upsize

By default, the SQL Server Upsizing Wizard exports a table's structure and its data. Along with field names and data types, you can also export:

Indexes
Defaults
Relationships (referential integrity constraints)
Validation rules

The SQL Server Upsizing Wizard can export additional table properties and create timestamp columns on certain SQL Server tables. The SQL Server Upsizing Wizard can also modify your Visual FoxPro database so that your queries, forms, and reports use the data in your new SQL Server tables, rather than the data in the local Visual FoxPro database.

Visual FoxPro indexes and defaults become SQL Server indexes and defaults. If you choose to export validation rules, the SQL Server Upsizing Wizard attempts to export field- and row-level validation rules to SQL Server, where they become stored procedures called from SQL Server triggers. Exported table relationships also become part of the triggers.

Tip If you export table relationships, be sure to export indexes as well or you might experience mediocre performance.

Table Attributes to Upsize

Indexes
Select to upsize Visual FoxPro .cdx indexes.
Defaults
Select to upsize default values for table fields.
Relationships
Select to upsize relationships stored in the database you are upsizing.
Validation rules
Select to upsize field and table validation rules.
Structure only, no data
Select to upsize the empty table structure, without copying table data to the SQL Server data source.
Use declarative referential integrity
Enabled when upsizing to a SQL Server version 6.x database. Select to create SQL constraints to enforce referential integrity.
Null Mapping
Select the fields that can accept .NULL.

This option helps to ensure that insert and update commands against remote data succeed. This option allows you to override the existing null setting fields in the Visual FoxPro tables being upsized. If you choose the General and Memo fields option, then all general and memo fields in your selected Visual FoxPro tables will be upsized with these fields set to allow nulls.

There are differences in how Visual FoxPro SQL commands such as INSERT and UPDATE affect local and remote data depending on the null settings for fields. For example, if you have an INSERT command that does not include all the fields in a table, Visual FoxPro inserts blanks into the unspecified fields for local data. If the INSERT command is issued for a remote view (SQL Server data), then nulls are inserted into the fields not specified in the command. The command will fail if any one of these fields does not support nulls. It's fairly easy to write an INSERT command, which includes all the fields and values to be inserted. Note, however, that inserting content into a general field is a little more cumbersome.

Changes to Make Locally

Create upsizing report
Creates a series of reports documenting the results of the upsizing process.
Redirect views to remote data
Changes the definition of local views in the database you are upsizing to remote views that use the upsized server tables as base tables. Your queries, forms, and reports then use views on the data in the new SQL Server data source rather than the original Visual FoxPro data.
Create remote views on tables
When you upsize a local table, you need a remote view to access the upsized table on the remote server. The SQL Server Upsizing Wizard can create new, remote views as you upsize. As the SQL Server Upsizing Wizard creates new remote views, it renames all local versions of tables by adding the suffix "_local" to the table name.

Note Creating remote views on tables as you upsize is not recommended, because these unoptimized views select all the data in the table rather than selecting only the information your application needs. The preferred method is to create parameterized views. You can either create a local parameterized view, which you then upsize by choosing Redirect views to remote data, or create a new parameterized remote view on the table after you've completed the upsizing process. For information on creating parameterized views, see Creating Views.
Save password with views
By default, you must enter your password and login ID when opening a remote view in a new session with Visual FoxPro. Select this option to store your password locally with the remote view definition in your database.

Note This check box is automatically checked for you and disabled if you used a named connection in SQL Server Upsizing Wizard: Step 2 - Select Data Source to access your data source, and that named connection includes a password. This prevents the password from being removed from the connection definition.

Creating Upsizing Reports

When you select Create Upsizing Report, Visual FoxPro generates upsizing reports that document the tables, views, fields, indexes, and referential integrity constraints the SQL Server Upsizing Wizard creates on SQL Server. The SQL Server Upsizing Wizard places the reports in a new project, using the following report names:

RptErrors
RptField
RptIndex
RptRels
RptTable
RptViews

The upsizing reports include information about any devices and databases that are created, information about any errors encountered during the upsizing process, and a complete explanation of the way in which each Visual FoxPro object is mapped to a SQL Server object. You can view or print these reports after upsizing is complete.

Upsizing All Tables Used By a Local View

If you upsize all the tables used by a local view, the local view is renamed by adding the suffix "_local" to the existing local view name. The SQL Server Upsizing Wizard creates a new view based on the SQL statement of the local view, substituting the remote table names for the local table names. The KeyField and other update properties of the view are preserved.

Upsizing Some of the Tables Used By a Local View

If you upsize only some of the tables used by a local view, the SQL Server Upsizing Wizard does not rename the local views. Instead, it renames the Visual FoxPro tables you export with the suffix "_local." For example, if you export a table named "Employees," the table is renamed "Employees_local" in your database. The SQL Server Upsizing Wizard then creates a remote view for each of the tables you exported. Each view selects all fields and all records from the remote table.

Note Visual FoxPro handles local and remote data in different ways. The data access design of an application using local data may become very inefficient when applied as is to remote data.

Advanced dialog box

Make Primary Key a Clustered Index
By default, tables that have a primary key automatically get a clustered index created on SQL Server by the Upsizing Wizard. This is because it is a SQL Server default if the [CLUSTERED | NONCLUSTERED] clause is not specified in the ALTER TABLE statement, which the Wizard uses. Being that primary keys are often already in sequential order, a clustered index may not be needed. This option allows you to control whether a clustered index is created.
Drop Local Tables
If you choose to create remote views for your tables, the Upsizing Wizard will create a new set of remote views using the names of their corresponding tables (and rename the original tables). It is possible that you no longer need to use the local tables. This option allows you to drop the local tables from the database.
Default Remote View Name
If you choose to create remote views, you may want to control the naming of these tables. By default, the name used is that of the original table (and the original table is renamed). The following options allow you to control the naming of remote views.
- Prefix specifies a character string to prepend to each of the newly created remote view names.
- Suffix specifies a character string to add to the end each of the newly created remote view names.
- None (same as local table name) defaults to using table name, and renames original table.

Share via