How the SQL Server Upsizing Wizard Works
The SQL Server Upsizing Wizard makes upsizing a Visual FoxPro database to SQL Server easy and almost transparent.
The SQL Server Upsizing Wizard exports data using one of two methods:
Create a stored procedure that performs multiple row inserts.
Create a SQL INSERT statement for each row in the table and then executes the statement.
Creating a stored procedure can be very fast because stored procedures are precompiled and execute quickly. However, stored procedures cannot accept variable length binary variables as parameters.
If you are exporting data that you want to store in SQL Server tables and uses text or image data types or tables with more than 250 fields, the SQL Server Upsizing Wizard creates a SQL INSERT statement for each row in the table and then executes the statement. If the SQL Server Upsizing Wizard encounters any errors, 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. However, the exported server table is not dropped, and any records that were successfully exported are left in the server table.
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 the following occur:
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 in the directory defined by the SET DEFAULT command for your Visual FoxPro session. The wizard adds the database tables used to produce the Upsizing Report, a table to store the generated SQL, and any error tables.
The following table lists the table files potentially created by the upsizing process.
Table name |
Contents |
Purpose |
|---|---|---|
Errors_uw |
Information on any error that occurred during upsizing. |
Report table |
Fields_uw |
Information about all the tables upsized. |
Report table |
Indexes_uw |
Information about all the indexes upsized. |
Report table |
Misc_uw |
Miscellaneous upsizing information. |
Report table |
Relations_uw |
Information about all the referential integrity constraints stored in the Visual FoxPro database. |
Report table |
Tables_uw |
Information on all the tables in the database you choose to upsize. |
Report table |
Views_uw |
Information about the local views redirected to access remote data. |
Report table |
SQL_uw |
One memo field containing all the SQL code generated by the SQL Server Upsizing Wizard. |
Script table |
ExportErrors_table_name |
The SQL Server Upsizing Wizard generates a table containing the records that are not successfully exported for each table that experiences a data export error during upsizing. |
Data export error tables |
Note
If the wizard is canceled during processing or if the wizard halts because of an error, no tables are left on your hard disk.
Tip
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 cannot run the entire SQL script as a substitute for running the SQL Server Upsizing Wizard because the wizard performs additional steps that are not reflected in the generated SQL code.
See Also
Tasks
How to: Start the SQL Server Upsizing Wizard