Mapping Visual FoxPro Databases to SQL Server Databases
The following sections contain information about mapping a Visual FoxPro database to a SQL Server database:
Mapping Objects
A Visual FoxPro database maps directly to a SQL Server database. When upsizing a Visual FoxPro database to a server, the SQL Server Upsizing Wizard creates server objects with as much as the same functionality possible as the Visual FoxPro database.
Mapping some Visual FoxPro objects to server objects is very straightforward. Visual FoxPro databases, tables, fields, defaults, and indexes map on a direct one-to-one basis to SQL Server databases, tables, fields, defaults, and indexes. A Visual FoxPro table maps to a SQL Server table with the exception of part of its data dictionary.
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 are not 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 |
Naming Conventions for Upsized Objects
When migrating 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, for example, defaults and rules, because no such standalone object existed in Visual FoxPro. A table name and then a field name, if appropriate, follow the prefix. This naming convention makes it possible for 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.
Note
If SQL Server naming conventions are violated, database, table, index, and field names might change during upsizing. 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.
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.
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.
The following table illustrates how Visual FoxPro data types map to SQL Server data types.
Abbreviation |
Visual FoxPro data type |
SQL Server data type |
|---|---|---|
C |
Character |
char |
Y |
Currency |
money |
D |
Date |
datetime |
T |
DateTime |
datetime |
B |
Double |
float |
F |
Float |
float |
G |
General |
image |
I |
Integer |
int |
L |
Logical |
bit |
M |
Memo |
text |
M (Binary) |
Memo (Binary) |
image |
C (Binary) |
Character (Binary) |
binary |
N |
Numeric |
float |
Timestamp columns are created using the Transact-SQL timestamp data type. 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.
Mapping Views
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 SQL Server Defaults
A Visual FoxPro default expression maps directly to a single SQL Server default. The SQL Server Upsizing Wizard attempts 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 more information, see Mapping Expressions.
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.
Though Visual FoxPro logical fields accept null values, SQL Server do not accept 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 (False (.F.) in Visual FoxPro) when you do not 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 does not 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.
Note
SQL Server defaults behave differently than Visual FoxPro defaults.
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 an order amount field in a customer table might be named "Dflt_Customer_Ordamt" on the server.
Note
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 but are functionally identical.
Index Conversion
SQL Server and Visual FoxPro indexes are very similar. The following table shows how Visual FoxPro index types convert to SQL Server index types.
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 does not 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.
Mapping 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.
Exporting 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.
Exporting 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.
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.
A Visual FoxPro persistent relationship 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 is not enforced in Visual FoxPro, the relationship is not exported.
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 |
Description |
|---|---|---|
UPDATE |
Cascade |
Cascade UPDATE trigger. |
UPDATE |
Restrict |
Restrict UPDATE trigger. |
UPDATE |
Ignore |
No trigger generated. |
INSERT |
Restrict |
Restrict INSERT trigger. |
INSERT |
Ignore |
No trigger generated. |
DELETE |
Cascade |
Cascade DELETE trigger. |
DELETE |
Restrict |
Restrict DELETE trigger. |
DELETE |
Ignore |
No trigger generated. |
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
At run time, if 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 whether the user was attempting to update, insert, or delete.
The following table lists the error numbers generated for each action.
Action |
SQL Server error number |
|---|---|
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.
Mapping Expressions
Although Visual FoxPro and Transact-SQL have some functions in common, many Visual FoxPro functions are not supported by SQL Server.
The following table lists those expressions that are the same between Visual FoxPro and SQL server.
CEILING( ) |
LOG( ) |
LOWER( ) |
LTRIM( ) |
RIGHT( ) |
RTRIM( ) |
SOUNDEX( ) |
SPACE( ) |
STR( ) |
STUFF( ) |
UPPER( ) |
|
The following table describes the expression mapping that the SQL Server Upsizing Wizard uses to attempt converting Visual FoxPro expressions in field and record-level validation rules and default values to Transact-SQL.
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, ...) |
TTOC( ) |
CONVERT(datetime, ...) |
YEAR( ) |
DATEPART(yy, ...) |
See Also
Tasks
How to: Start the SQL Server Upsizing Wizard
Concepts
How the SQL Server Upsizing Wizard Works