Chapter 20 -- Upsizing Visual FoxPro Databases
Once you ve designed your client/server application, you re ready to build and upsize a local prototype. A local prototype is a working model of your application using Visual FoxPro tables, views, and databases to represent data that will eventually be accessed on a remote server. Use the upsizing wizards to move databases, tables, and views from your system to a remote SQL Server or Oracle server.
This chapter discusses:
- Goals for Prototyping
- Building a Local Prototype of an Application
- Using the Upsizing Wizards
- Upsizing to SQL Server
- Upsizing to Oracle
Goals for Prototyping
When you use Visual FoxPro to build a prototype of your application, you re leveraging the power of visual forms, wizards, builders, designers, and the Project Manager to quickly develop a working application. While your ultimate goal is to implement your application across client/server platforms, you gain a great deal by choosing to build a solid prototype.
Reducing Development Time
By building a quick prototype, you can refine your application s design and local architecture quickly and easily, without having to access the remote server to rebuild server tables and databases. You can also test and debug your application s forms against smaller data stores, allowing you to more quickly correct and enhance your application s user interface. Because you re keeping architectural overhead low, you prevent wasting development time in rebuilding, reindexing, and reconnecting remote data just to test your prototype.
Decreasing Development Costs While Increasing Customer Satisfaction
Because the local prototype is self-contained on your computer, you can easily demonstrate a working model of your application to the end user early in the development cycle. Being able to see your application as it progresses gives clients confidence in your ability to deliver a solution that meets their needs. It also provides you with the opportunity to get customer feedback about the user interface and reports before you ve invested resources in implementing it against a remote server.
As users see and interact with your prototype, they can begin to identify areas they d like to change, as well as see the potential for adding additional functionality into their application. You can implement changes and redemonstrate the application in an iterative process until both you and the customer are satisfied with the design and function of the prototyped application. Your prototype then serves as a working specification for the final, implemented client/server application.
Contributing to Successful Implementation
You can also potentially provide the prototyped application as a demonstration for your users, allowing them to experiment with the working model as you move forward in the implementation process of the actual application. As they gain experience with the prototype, their learning curve decreases and they become better partners with you in refining and fine-tuning the application. They're also positioned to be more productive and satisfied in the final implementation stage because they already understand the basic framework of the application.
Having a working model increases the lead time the end user has to become familiar and comfortable with the application. It also provides a framework to allow staff at your company or the customer site to design and develop a training plan for the application. The prototype can even be used to train end users before the final application is delivered, thus contributing to a successful implementation of the final, implemented client/server application.
Building a Local Prototype of an Application
When you build a local prototype of your application, you might be starting from scratch, or you might be converting an existing Visual FoxPro application to a client/server application. The primary difference between building a local prototype of a client/server application and developing any other Visual FoxPro application is in using local views and tables to represent data that will eventually be upsized.
To build and upsize a local prototype
- Create your application using local views and local tables to represent data you want to move to a remote server.
- Use local views in your application s data environment for forms and reports.
- Upsize local views and tables using the SQL Server Upsizing Wizard or Oracle Upsizing Wizard:
- In the Set Upsizing Options step, in the Changes to make locally area, select Redirect views to remote data.
When you select this option, the upsizing wizard copies the local tables you select to the remote server, and redirects local views to use remote data where applicable.
For more information on creating views, see Chapter 8, Creating Views. For information on creating forms and using a data environment, see Chapter 9, Creating Forms. For information on developing an application, see Chapter 2, Developing an Application.
Using the Upsizing Wizards
Visual FoxPro provides two upsizing wizards: the SQL Server Upsizing Wizard, and the Oracle Upsizing Wizard. These wizards create SQL Server or Oracle databases that duplicate, as much as possible, the functionality of a set of tables in a Visual FoxPro database. You can also choose to redirect Visual FoxPro views so that they use the newly created remote data instead of local data. You can use the upsizing wizards to:
- Move local data to a remote server.
- Transform local base tables and local views into remote base tables and remote views.
- Migrate a local application to a client/server application.
Note Though the upsizing wizards access SQL Server or Oracle servers, you can create a client/server application for any remote ODBC data source. For servers other than SQL Server or Oracle, you can use SQL pass-through functions to create remote tables, then use Visual FoxPro to create remote views that access server tables. For more information on using SQL pass-through functions, see Chapter 21, Implementing a Client/Server Application. For information on creating remote views, see Chapter 8, Creating Views.
Upsizing to SQL Server
Before you run the SQL Server Upsizing Wizard, you must prepare both the client and server sides.
Preparing the SQL Server Side
Before upsizing, you must ensure that you have necessary permissions on the server, estimate the size of your database, and check that the server has sufficient disk space. There are also special preparations for upsizing to multiple disks or devices.
Checking Free Disk Space
Make sure you have enough disk space on the server.
Caution If the SQL Server Upsizing Wizard runs out of disk space on the server, it halts, leaving a partial database and any devices it created on the server. You can remove devices, databases, and tables with the SQL Server Administration tool.
Setting Permissions on SQL Server Databases
In order to run the SQL Server Upsizing Wizard, you must have certain permissions on the SQL server to which you will be upsizing. The permissions you need depend on the tasks you want to accomplish.
- To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.
- To build a new database, you need CREATE DATABASE and SELECT permissions on the system tables in the master database.
- To create new devices, you must be a system administrator.
For more information on granting server permissions, see your SQL Server documentation.
Estimating SQL Server Database and Device Size
When you create a new database, the SQL Server Upsizing Wizard asks you to select devices for your database and log. It also asks you to set the size of the database and your devices.
Estimating SQL Server Database Size
When SQL Server creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all this space is necessarily used by the database the database size simply limits how large a database can grow before it runs out of space.
Note You can increase the size of a SQL Server database after it s created. For more information, see the ALTER DATABASE command in your SQL Server documentation.
To estimate the space needed for your database, calculate the total size of your Visual FoxPro .dbf files for the tables you want to upsize plus the rate at which your new SQL Server database will grow. In general, every megabyte of Visual FoxPro data requires at least 1.3 to 1.5 MB in the SQL Server.
If you have ample disk space on your server, multiply the size of your Visual FoxPro tables by two. This ensures that the SQL Server Upsizing Wizard has enough space to upsize your database and also leaves some room to grow. If you expect to add a lot of data to the database, increase the multiple.
Estimating SQL Server Device Sizes
All SQL Server databases and logs are placed on devices. A device is both a logical location at which to put databases and logs, and a physical file. When a device is created, SQL Server creates a file, thus reserving a set amount of disk space for its own use.
The SQL Server Upsizing Wizard displays the amount of free space available on the existing SQL Server devices. Select a device that has at least enough free space for your estimated database size.
If no existing devices have enough free space, you can use the SQL Server Upsizing Wizard to create a new device. New devices should be at least as big as your estimated database size. If possible, make the device larger than your database size, so you can expand your database later, or place other databases or logs on the same device.
Important Device size cannot be changed. Make sure you create sufficiently large devices.
Using Multiple SQL Server Disks or Devices
In most cases, the SQL Server Upsizing Wizard provides more than enough control over SQL Server devices. However, if your server has multiple disks or if you want to place a database or log on multiple devices, you might want to create devices before running the SQL Server Upsizing Wizard.
Servers with Multiple Physical Disks
If your server has more than one physical hard disk, you might want to place your database on one disk and the database log on a different disk. In the event of a disk failure, you ll be more likely to recover your database if the log and the database are stored on different physical disks.
The SQL Server Upsizing Wizard allows you to create new devices but only on one physical disk the same disk as the Master database device.
To place a database and log on separate disks, make sure you have devices that are big enough on both disks, creating new devices if necessary. Then run the SQL Server Upsizing Wizard.
Placing Databases or Logs on Multiple Devices
SQL Server allows databases and logs to span several devices. However, the SQL Server Upsizing Wizard allows you to specify only one device for your database and one device for the log.
If you want to specify multiple devices for a database or log, make those devices (and no other devices) the default devices. Then run the SQL Server Upsizing Wizard and choose Default for the database or log device.
Note If the new SQL Server database or log sizes don t require using all the default devices, SQL Server uses only the devices necessary to accommodate the database or log.
Preparing the Client
Before upsizing, you must have access to a SQL Server through an ODBC data source or named connection. You must also have a Visual FoxPro database, which you should back up before running the SQL Server Upsizing Wizard.
Creating an ODBC Data Source or Named Connection
When you create a new remote database, you select an ODBC data source or named connection in your Visual FoxPro database that accesses the SQL Server to which you want to upsize. Because you can t proceed through the Upsizing Wizard until you select a named connection or data source, you should create the appropriate named connection or data source before you start the upsizing process.
For information on creating a named connection, see Chapter 8, Creating Views. If you want to create an ODBC data source, run the ODBC Administrator. For information on setting up ODBC data sources, see Chapter 1, Installing Visual FoxPro, in the Installation Guide.
Backing Up Your Database
It's a good idea to create a backup copy of your database (.dbc, .dct, and .dcx files) before upsizing. While the SQL Server Upsizing Wizard doesn't modify .dbf files, it does operate on the .dbc directly by opening the .dbc as a table at times and indirectly by renaming tables and views when creating new remote views. If you back up your database, you can revert your database to its original pre-upsizing state by overwriting the upsized .dbc, .dct, and .dcx files with the original backup copies, which reverses the renaming and creation of new views.
Closing Tables
The SQL Server Upsizing Wizard attempts to exclusively open all the tables in the database to be upsized; if any tables are already open and shared, the wizard closes them and reopens them exclusively. Opening tables exclusively before upsizing protects against other users attempting to modify records in the tables you're exporting during data export. If any tables can t be opened exclusively, the SQL Server Upsizing Wizard displays a message; those tables are not available for upsizing.
Starting the SQL Server Upsizing Wizard
After you create an ODBC data source and complete the necessary preparations on the client and server, you're ready to begin upsizing.
To start the SQL Server Upsizing Wizard
- From the Tools menu, choose Wizards, and then choose Upsizing.
- From the Wizard Selection dialog box, choose SQL Server Upsizing Wizard.
- Follow the directions in the wizard screens, as described in the following sections.
You can choose Cancel at any time to exit the wizard; the wizard performs no actions on the server until you choose Finish.
- When you're ready to upsize, choose Finish.
After you choose Finish, the SQL Server Upsizing Wizard begins exporting the database to the server.
The Finish button is available after you provide the basic information needed for upsizing. If you choose Finish before you complete all the wizard screens, the SQL Server Upsizing Wizard uses default values for the remaining screens.
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 later in this chapter.
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 chapter.
SQL Server defaults behave differently than Visual FoxPro defaults. For more information, see Default Values later in this chapter.
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 ConstraintSQL 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.
Completing the SQL Server Upsizing Process
You can now take additional steps, both on your server and in your Visual FoxPro application, to ensure your application and data are secure and functioning properly.
You can also use the information in this section when you build an application from remote views rather than by upsizing. Regardless of how you created remote tables, you take certain steps to ensure the server and client are prepared to work together in your client/server application.
SQL Server Steps
You can complete the upsizing process on your server by:
- Making sure the tables you want to edit from Visual FoxPro are updatable.
- Setting permissions on the database so that users can access the objects they need.
- Protecting your work by making your new database recoverable, in case it's damaged or lost.
Adding Unique Indexes for Updatability
A remote table should have a unique index to be updatable in Visual FoxPro. The SQL Server Upsizing Wizard can export an existing unique index, but it doesn't create one where none exists. Make sure that tables you want to edit from Visual FoxPro are updatable.
Setting Permissions
The new SQL Server database and its objects receive a set of default permissions from the SQL Server. Set permissions on the remote database so that your users have access to the objects they need.
Database Logon Permissions
The default permissions of a new database make it accessible only to system administrators and the database owner.
You can add new users and groups by using the SQL Server Security Manager or the system procedures sp_adduser and sp_addgroup.
For more information on adding users and groups, see SQL Server Security Manager Help and the documentation of the system procedures sp_adduser and sp_addgroup in the Microsoft SQL Server Transact-SQL Reference.
Object Permissions
All objects created by the Visual FoxPro-to-SQL Server Upsizing Wizard, including tables, triggers, and defaults, are accessible initially only to the database owner and system administrators. This is true whether you upsized to a new or existing database. If you overwrite existing objects, you also overwrite all object permissions.
To grant permissions on tables, use SQL Enterprise Manager or the GRANT and REVOKE commands. For more information on setting object permissions, see the Managing Object Permissions section in the Microsoft SQL Server Administrator's Companion, and the GRANT and REVOKE commands in the Microsoft SQL Server Transact-SQL Reference.
Ensuring Recoverability
Protect your work by making your new database recoverable in case it's damaged or lost.
Dumping the Master Database
When a database is created on a SQL Server, new records are added to the system tables in the Master database. Dumping the Master database also provides you with a backup copy, including all the latest changes. For more information on dumping the Master database, see Backing Up the Master Database in the Microsoft SQL Server Administrator's Companion, and the DUMP Statement and Dumping the Master Database in the Microsoft SQL Server Transact-SQL Reference.
Scheduling Backups
Schedule regular backups of your database so that you can restore your database from this backup copy in the event of a serious problem. For details on backing up SQL Server databases, see Database Maintenance Plan Wizard and Backup and Recovery in What's New in SQL Server 6.5 and Database Design and Backup Strategy in the Microsoft SQL Server Transact-SQL Reference.
Device Mirroring
Mirroring a device continuously duplicates the information from one SQL Server device to another. In the event that one device fails, the other contains an up-to-date copy of all transactions.
If you anticipate that many changes will be made to a database between backups and you can t afford to lose those changes, consider device mirroring. Device mirroring is most effective when the devices are located on separate disks, as both devices might be lost if they're on the same disk and the disk fails.
For more information on mirroring devices, see Mirroring a Database Device, About SQL Server Device Mirroring, and Using SQL Server Mirroring in the Microsoft SQL Server Administrator's Companion.
Visual FoxPro Client Steps
Once you've transferred objects from Visual FoxPro to a SQL Server, you probably need to modify code in the original Visual FoxPro database so that it functions properly with the new SQL Server database.
Optimizing Views
Views created by the SQL Server Upsizing Wizard aren't parameterized and therefore are not optimized. For most efficient processing, add parameters to views created by the SQL Server Upsizing Wizard to download just the data you need. For information on adding a parameter to a view, see Chapter 8, Creating Views.
Some Visual FoxPro functions aren't supported by SQL Server. If the remote view created by the SQL Server Upsizing Wizard uses functions that couldn't be mapped to Transact-SQL functions, the view will not work. For more information on mapping Visual FoxPro expressions to Transact-SQL expressions, see Expression Mapping earlier in this chapter.
Creating Stored Procedures and Triggers
The SQL Server Upsizing Wizard doesn't upsize Visual FoxPro stored procedures and triggers. If you want to create SQL Server stored procedures or triggers, you can use Transact-SQL on the server or use SQL pass-through in Visual FoxPro. For more information on using Transact-SQL, see your SQL Server documentation. For information on using SQL pass-through, see Chapter 21, Implementing A Client/Server Application.
Comparing Event Order
In Visual FoxPro, some events occur in a different order, depending on whether your application is using SQL Server data or Visual FoxPro data. These differences might require changes to your code.
Default Values
Visual FoxPro default field values appear when you begin editing a new record. Default values generated by SQL Server defaults appear only after a record has been inserted. You need to change any code that depends on having values before the record is committed, such as the code for lookups.
Validation Rules
In Visual FoxPro, field validation occurs when the focus leaves a field. When you edit SQL Server data in attached tables, triggers and rules aren't fired until you leave the record. You might need to modify any record validation rules that rely on field validation occurring when a field is exited.
Handling Unconverted Expressions
The upsizing report indicates whether each Visual FoxPro table validation rule, field validation rule, and default expression was successfully converted. If a default expression or validation rule was not translated, you should rewrite it in Transact-SQL.
You can also perform validation at the form level in Visual FoxPro. However, if server data is then modified without using a particular form, the validation will not be applied and invalid data might be entered.
For more information about expression conversion, see Expression Mapping earlier in this chapter. For more information about Transact-SQL functions, see your SQL Server documentation.
Record Locking
Visual FoxPro uses optimistic locking internally when accessing tables on a SQL server. Optimistic locking means that the row is locked only when the edited value is committed and the update process occurs usually a very brief interval.
Optimistic locking is used rather than pessimistic locking on SQL Server because pessimistic locking on SQL Server is provided by page locking, potentially locking many records at a time. While page locking prevents other users from making changes to the same record you re editing, it can also prevent users from accessing many other records in the same (locked) page. Optimistic locking provides the best multi-user access for a Visual FoxPro client/server application.
You can optimize updates and control how update conflicts are handled with the SQL WhereType property. For more information on controlling update conflicts, see Chapter 8, Creating Views.
Upsizing to Oracle
The Oracle Upsizing Wizard is similar in behavior to the SQL Server Upsizing Wizard. For step-by-step instructions, see Oracle Upsizing Wizard. For specific information on Oracle servers, see your Oracle documentation.
Starting the Oracle Upsizing Wizard
After you create a named connection or ODBC data source connecting to an Oracle server, and complete the necessary preparations on the client and server, you're ready to begin upsizing.
To start the Oracle Upsizing Wizard
- From the Tools menu, choose Wizards, and then choose Upsizing.
- From the Wizard Selection dialog box, choose Oracle Upsizing Wizard.
- Follow the directions in the wizard screens.
You can choose Cancel at any time to exit the wizard; the wizard performs no actions on the server until you choose Finish.
- When you're ready to upsize, choose Finish.
Finish is available after you provide the basic information needed for upsizing. If you choose Finish before you complete all the wizard screens, the Oracle Upsizing Wizard uses default values for the remaining screens.
After you choose Finish, the Oracle Upsizing Wizard begins exporting the database to the server.
EAN: N/A
Pages: 58