How the SQL Server Upsizing Wizard Works
The SQL Server Upsizing Wizard makes upsizing a Visual FoxPro database to SQL Server practically transparent. This section explains exactly what happens after you choose Finish — how the SQL Server Upsizing Wizard exports data and maps Visual FoxPro objects to SQL Server objects.
Data Export Methods
The SQL Server Upsizing Wizard exports data using one of two methods. The first method creates a stored procedure that performs multiple row inserts. This method can be very fast, because stored procedures are precompiled and execute quickly.
However, stored procedures can't accept variable length binary variables as parameters. If you're exporting data that's to be stored in SQL Server tables using text or image data types, or tables with more than 250 fields, the SQL Server Upsizing Wizard uses a different exporting method. This second method creates a SQL INSERT statement for each row in the table and then executes the statement.
If the SQL Server Upsizing Wizard encounters errors while exporting data using the SQL INSERT method, and the number of errors exceeds 10 percent of the number of records in the table or 100 records (whichever is larger), the wizard cancels the export for that table and saves the number of export errors for the error report. The exported server table is not dropped, however, and any records that were successfully exported are left in the server table.
Overview of Object Mapping
To upsize a Visual FoxPro database to a server, the SQL Server Upsizing Wizard creates server objects that, as far as possible, do everything the Visual FoxPro database did. Mapping some Visual FoxPro objects to server objects is very straightforward: Visual FoxPro databases, tables, fields, defaults, and indexes map to SQL Server databases, tables, fields, defaults, and indexes in a direct, one-to-one mapping.
However, not all local objects map directly to server objects. Validation rules and referential integrity in Visual FoxPro are part of the data dictionary and are enforced at the engine level. SQL Server validation rules and referential integrity aren't part of the data dictionary, and are enforced through code bound to a table. These differences, as well as design decisions made by the SQL Server Upsizing Wizard, mean that much of the Visual FoxPro data dictionary cannot be mapped directly to SQL Server constructs.
The following table summarizes how objects are mapped from Visual FoxPro to SQL Server:
| Visual FoxPro objects | SQL Server objects |
|---|---|
| Database | Database |
| Table | Table |
| Indexes | Indexes |
| Field | Field |
| Default | Default |
| Table validation rule | SQL Server stored procedures, called from UPDATE and INSERT triggers |
| Field validation rule | SQL Server stored procedures, called from UPDATE and INSERT triggers |
| Persistent relationships (where used for referential integrity constraints) | Update, Insert, and Delete triggers or table constraints |
The following sections discuss each Visual FoxPro object and the SQL Server object (or objects) to which it maps.
Naming Conventions for Upsized Objects
As it migrates objects to a data source, the SQL Server Upsizing Wizard creates named objects on the server. The wizard uses prefixes for objects that need new names because no such stand-alone object existed in Visual FoxPro (defaults and rules, for example). Following the prefix is a table name and then a field name, if appropriate. This naming convention enables all objects of the same kind to have the same prefix and sort together when viewed with data source administration tools. Objects created on the same table also group together when viewed.
Database and Table Objects
A Visual FoxPro database maps directly to a SQL Server database. A Visual FoxPro table, excluding part of its data dictionary, maps to a SQL Server table.
Database, table, index, and field names might change during upsizing, if they violate SQL Server naming conventions. SQL Server names must be 30 characters or less, and the first character must be a letter or the symbol "@". The remaining characters may be numbers, letters, or the "$", "#", and "_" symbols; no spaces are allowed. The SQL Server Upsizing Wizard replaces any illegal characters with the "_" symbol.
Any names that are identical to SQL Server reserved words are given a suffix of "_". For example, FROM and GROUP become FROM_ and GROUP_. The SQL Server Upsizing Wizard also places the "_" symbol in front of object names that begin with a number.
Tables
The SQL Server Upsizing Wizard gives each upsized table the same name as the local table unless the table name contains a space or is a keyword for the data source.
Views of New Server Tables
If you select Create Remote Views On Tables, the SQL Server Upsizing Wizard creates remote views and gives them many of the properties of the fields in the original local table.
Mapping Visual FoxPro Field Names and Data Types to SQL Server Equivalents
Field names and data types are automatically translated into SQL Server fields when a Visual FoxPro table is exported by the SQL Server Upsizing Wizard.
Visual FoxPro data types map to SQL Server data types as follows:
| Abbreviation | Visual FoxPro Data Type | SQL Server Data Type |
|---|---|---|
| C | Character | |
| Y | Currency | |
| D | Date | |
| T | DateTime | |
| B | Double | |
| F | Float | |
| G | General | |
| I | Integer | |
| L | Logical | |
| M | Memo | |
| M (binary) | Memo binary | |
| C (binary) | Character binary | |
| N | Numeric | |
Timestamp and Identity Columns
Timestamp columns are created using the Transact-SQL timestamp datatype. When you select the Timestamp column checkbox for a specific table in Step 4 - Map Field Data Types, the SQL Server Upsizing Wizard creates a timestamp field for that table.
If a table contains one or more memo (M) or picture (P) fields, the SQL Server Upsizing Wizard selects the Timestamp column check box for that table by default and creates a timestamp field on the upsized version of the table.
Identity columns are created using the Transact-SQL IDENTITY property fields.
Indexes
SQL Server and Visual FoxPro indexes are very similar. The following table shows how Visual FoxPro index types are converted to SQL Server index types:
Index Type Conversion
| Visual FoxPro Index Type | SQL Server Index Type |
|---|---|
| Primary | Clustered Unique |
| Candidate | Unique |
| Unique
Regular |
Non-unique |
The SQL Server Upsizing Wizard uses Visual FoxPro tag names as names for indexes on SQL Server. If the tag name is a reserved word on the server, the wizard alters the tag name by appending the "_" character.
Note SQL Server doesn't support ascending or descending indexes, or permit expressions within server indexes. The SQL Server Upsizing Wizard removes Visual FoxPro expressions from index expressions as the index is upsized; only field names are sent to the server.
SQL Server Defaults
A Visual FoxPro default expression maps directly to a single SQL Server default. The SQL Server Upsizing Wizard tries to create a SQL Server default based on the default expression for a Visual FoxPro field. If the default is successfully created, the SQL Server Upsizing Wizard binds it to the appropriate SQL Server field. The upsizing report on fields indicates if the SQL Server Upsizing Wizard was successful in translating the Visual FoxPro expression to SQL Server Transact-SQL. For details on translation, see Expression Mapping.
While SQL Server and Visual FoxPro defaults are largely similar, there are some differences in the way defaults are created and behave in the two products. SQL Server defaults are stand-alone objects, independent of any particular field or table. Once a default has been created, it can be used by, or bound, to any number of different fields.
Naming Conventions for Defaults
The SQL Server Upsizing Wizard names defaults using the prefix Dflt_ plus the table name and field name. For example, a default value for the ordamt field in the Customer table might be named Dflt_Customer_Ordamt on the server. If combining the prefix with the table and field names causes the default name to exceed 30 characters, Visual FoxPro truncates the excess characters.
Fields with a default expression of zero are bound to a default named UW_ZeroDefault. If two or more fields have the same non-zero default expression, the SQL Server Upsizing Wizard creates two defaults, with two different names, that are functionally identical.
Default Values for Visual FoxPro Logical Fields
Logical fields in SQL Server prohibit null values; Visual FoxPro logical fields allow them. To manage this difference, the SQL Server Upsizing Wizard automatically creates and binds a default value called UW_ZeroDefault to each exported logical field, whether or not you chose to export defaults. This default sets the value of the server field to 0 (or false (.F.), if you look at the field in Visual FoxPro) when you don't supply a value.
If the local Visual FoxPro table contains a default value for a logical field that sets the field equal to true (.T.), the SQL Server Upsizing Wizard doesn't bind the UW_ZeroDefault default to the server table. Instead, the wizard creates a default that sets the field equal to 1, and names the default according to the naming conventions outlined earlier in this topic.
SQL Server defaults behave differently than Visual FoxPro defaults.
SQL Server Triggers
A SQL Server trigger is a series of Transact-SQL statements associated with a particular SQL Server table. When you choose to upsize Validation rules and Relationships in Step 8, the SQL Server Upsizing Wizard converts Visual FoxPro field- and record-level validation rules and persistent table relationships into stored procedures that are called from SQL Server triggers. Each server trigger can contain code to emulate the functionality of several validation and referential integrity rules.
Note The SQL Server Upsizing Wizard does not upsize Visual FoxPro triggers.
A server table can have three triggers, one for each of the commands that can modify data in the table: UPDATE, INSERT, and DELETE. The trigger is automatically executed when the associated command is carried out.
The following table describes the triggers created by the SQL Server Upsizing Wizard. Any specific trigger might contain code to emulate some or all of the Visual FoxPro functionality listed.
| Trigger | Visual FoxPro Functionality Emulated |
|---|---|
| UPDATE | Validation rules (Field- and record-level validation)
Referential integrity |
| INSERT | Validation rules (Field- and record-level validation)
Referential integrity (Child table triggers only) |
| DELETE (Parent table only) | Referential integrity |
Naming Conventions for Triggers
The SQL Server Upsizing Wizard names server triggers by combining a prefix that indicates the type of trigger being created with the table name of the SQL Server table to which the trigger belongs. The prefix ("TrigU_" for UPDATE triggers, "TrigD_" for DELETE triggers, and "TrigI_" for INSERT triggers) is placed in front of the table name. For example, the UPDATE trigger on the Customer table might be called TrigU_Customer.
Validation Rules
The SQL Server Upsizing Wizard can export Visual FoxPro field- and record-level validation rules, which it converts to stored procedures on SQL Server. The wizard names field-level rules by combining a prefix "vrf" (for "validation rule, field") with the names of the table and the field; an example might be vrf_customer_lname. Table validation rules are named with the prefix "vrt" (for "validation rule, table") plus the name of the table, to create a name such as vrt_customer.
The SQL Server Upsizing Wizard uses triggers that call stored procedures rather than SQL Server rules to enforce field level validation because SQL Server rules don't allow you to display custom error messages. For more information about SQL Server rules, see the CREATE RULE command in your SQL Server documentation.
Referential Integrity
Your Visual FoxPro application supports referential integrity through triggers on UPDATE, DELETE, and INSERT events on persistent table relationships that are enforced at the engine level. You can choose to implement referential integrity constraints on SQL Server using either of two methods:
Trigger-based referential integrity
-or-
Declarative referential integrity
When you choose trigger-based referential integrity, the SQL Server Upsizing Wizard creates triggers that include the Transact-SQL code required to duplicate Visual FoxPro referential integrity constraints. If you choose to implement declarative referential integrity, the SQL Server Upsizing Wizard creates SQL Server constraints using the ALTER TABLE command with the CONSTRAINT keyword.
Trigger-based Referential Integrity
In the trigger-based method, referential integrity is enforced on SQL Server by Transact-SQL code in triggers. You can use triggers to provide restrictions on UPDATE, DELETE, and INSERT statements, and to cascade changes resulting from DELETE and INSERT statements.
The SQL Server Upsizing Wizard creates SQL Server triggers by evaluating the Visual FoxPro triggers used to enforce referential integrity on persistent relationships in your Visual FoxPro database. The following table lists the mapping between Visual FoxPro Referential Integrity constraints and the SQL Server triggers generated by the SQL Server Upsizing Wizard.
Visual FoxPro Referential
Integrity Constraint SQL Server Trigger
| UPDATE | Cascade | Cascade UPDATE trigger |
| Restrict | Restrict UPDATE trigger | |
| Ignore | No trigger generated | |
| DELETE | Cascade | Cascade DELETE trigger |
| Restrict | Restrict DELETE trigger | |
| Ignore | No trigger generated | |
| INSERT | Restrict | Restrict INSERT trigger |
| Ignore | No trigger generated |
A Visual FoxPro persistent relationship that's used in a referential integrity constraint can become up to four triggers on a SQL Server data source: two for the parent table and two for the child table.
Note If only one of the tables in a relationship is upsized, or if referential integrity isn't enforced in Visual FoxPro, the relationship isn't exported.
Parent Table
The SQL Server Upsizing Wizard creates an UPDATE trigger that either prevents the user from changing the parent table's primary key or cascades that change through the child table, depending on the type of relationship that was created in Visual FoxPro.
The SQL Server Upsizing Wizard also creates a DELETE trigger that prevents the user from deleting a record with related child records, or that deletes the child records, again depending on the type of relationship between the tables in Visual FoxPro.
Child Table
The SQL Server Upsizing Wizard creates an UPDATE trigger that prevents the user from making changes to the foreign key that would orphan the record. Similarly, an INSERT trigger is created to prevent the user from adding a new record that has no parent.
Custom Error Values
If, at run time, the referential integrity established by the SQL Server Upsizing Wizard-created triggers is violated, SQL Server places a custom error value into the @@ERROR variable. Potential error values are defined by the SQL Server Upsizing Wizard as a part of the trigger code. The specific error value returned at run time depends on the action the user was attempting: updating, inserting, or deleting.
The following table lists the error numbers generated for each action:
| Action | SQL Server Error |
|---|---|
| Violated validation rule | 44444 |
| Attempted delete | 44445 |
| Attempted update | 44446 |
| Attempted insert | 44447 |
| Update or Delete statement affected more than one row; statement is automatically rolled back | 44448 |
Declarative Referential Integrity
If you choose to implement declarative referential integrity, the SQL Server Upsizing Wizard creates SQL Server constraints using the ALTER TABLE command with the CONSTRAINT keyword. The parent table constraint uses the PRIMARY KEY keyword. The child table constraint uses the FOREIGN KEY and REFERENCES keywords. Declarative referential integrity is supported at the RESTRICT, RESTRICT updates, and RESTRICT deletes levels.
You can use SQL Server constraints to provide restrictions on UPDATE, DELETE, and INSERT statements.
Expression Mapping
Although Visual FoxPro and Transact-SQL have some functions in common, many Visual FoxPro functions aren't supported by SQL Server. The SQL Server Upsizing Wizard attempts to convert Visual FoxPro expressions in field- and record-level validation rules and default values to Transact-SQL, using the following expression mapping.
| Visual FoxPro expression | SQL Server expression |
|---|---|
| True (.T.) | 1 |
| False (.F.) | 0 |
| # | <> |
| .AND. | AND |
| .NOT. | NOT |
| .NULL. | NULL |
| .OR. | OR |
| =< | <= |
| => | >= |
| ASC( ) | ASCII( ) |
| AT( ) | CHARINDEX( ) |
| CDOW( ) | DATENAME(dw, ...) |
| CHR( ) | CHAR( ) |
| CMONTH( ) | DATENAME(mm, ...) |
| CTOD( ) | CONVERT(datetime, ...) |
| CTOT( ) | CONVERT(datetime, ...) |
| DATE( ) | GETDATE( ) |
| DATETIME( ) | GETDATE( ) |
| DAY( ) | DATEPART(dd, ...) |
| DOW( ) | DATEPART(dw, ...) |
| DTOC( ) | CONVERT(varchar, ...) |
| DTOR( ) | RADIANS( ) |
| DTOT( ) | CONVERT(datetime, ...) |
| HOUR( ) | DATEPART(hh, ...) |
| LIKE( ) | PATINDEX( ) |
| MINUTE( ) | DATEPART(mi, ...) |
| MONTH( ) | DATEPART(mm, ...) |
| MTON( ) | CONVERT(money, ...) |
| NTOM( ) | CONVERT(float, ...) |
| RTOD( ) | DEGREES( ) |
| SUBSTR( ) | SUBSTRING( ) |
| TTOC( ) | CONVERT(char, ...) |
| TTOD( ) | CONVERT(datetime, ...) |
| YEAR( ) | DATEPART(yy, ...) |
The following expressions are the same on Visual FoxPro and on SQL Server.
Expressions that map directly from Visual FoxPro to SQL Server
| CEILING( ) | LOG( ) | LOWER( ) |
| LTRIM( ) | RIGHT( ) | RTRIM( ) |
| SOUNDEX( ) | SPACE( ) | STR( ) |
| STUFF( ) | UPPER( ) |
Files Created by the SQL Server Upsizing Wizard
The SQL Server Upsizing Wizard creates tables for its own use during the upsizing process. These files are removed from the hard disk unless:
- You choose to produce an upsizing report.
- You want to save the generated SQL.
- Errors occur during upsizing and you choose to save the error information.
If any of the conditions above are true, the SQL Server Upsizing Wizard creates a project (named Report, Report1, Report2, and so on) and a database (named Upsize, Upsize1, and so on) in a subdirectory (named UPSIZE) of the defined by the SET DEFAULT command for your Visual FoxPro session. The wizard adds to the database the tables used to produce the Upsizing Report, a table to store the generated SQL, and any error tables. The following table lists the files potentially created by the upsizing process.
Local Tables Created During Upsizing
| File Purpose | Table Name | Contents |
|---|---|---|
| Report Tables | Errors_uw | Information on any error that occurred during upsizing. |
| Fields_uw | Information about all the tables upsized. | |
| Indexes_uw | Information about all the indexes upsized. | |
| Misc_uw | Miscellaneous upsizing information. | |
| Relations_uw | Information about all the referential integrity constraints stored in the Visual FoxPro database. | |
| Tables_uw | Information on all the tables in the database you choose to upsize. | |
| Views_uw | Information about the local views redirected to access remote data. | |
| Script Table | SQL_uw | One memo field containing all the SQL code generated by the SQL Server Upsizing Wizard. |
| Data Export Error Tables | ExportErrors_table_name | For each table that experiences a data export error during upsizing, the SQL Server Upsizing Wizard generates a table containing the records that aren't successfully exported. |
If the wizard is canceled during processing or if the wizard halts because of an error, no tables are left on your hard disk.
Using Generated SQL
The Script table stored on your hard disk contains all the SQL code generated by the SQL Server Upsizing Wizard, whether it executes without error on the server or not. If you want to use this code, the best approach is to look at the generated SQL, copy the parts of it you want to use, run the extracted pieces of code, and repeat the process to obtain the results you want. You can't run the entire SQL script as a substitute for running the SQL Server Upsizing Wizard, because the wizard performs additional steps that aren't reflected in the generated SQL code.
See Also
Starting the SQL Server Upsizing Wizard | SQL Server Upsizing Process Completion | Upsizing Visual FoxPro Databases | Expression Mapping