DOCUMENT:Q130166  17-AUG-1999  [foxpro]
TITLE   :Data Type Mapping in the Upsizing Wizard
PRODUCT :Microsoft FoxPro
PROD/VER:3.00
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 3.0 
-------------------------------------------------------------------------------

SUMMARY
=======

Field names and data types are automatically translated into SQL Server fields
when a Visual FoxPro table is exported by the Upsizing Wizard. This article
lists the data type mapping that occurs between Visual FoxPro and SQL when the
data is exported from the Upsizing Wizard.

The information in this article is also available under many separate topics in
the Visual FoxPro Help file. Search using "Upsizing Wizard."

MORE INFORMATION
================

Field names and data types are automatically translated into SQL Server fields
when a Visual FoxPro table is exported by the Upsizing Wizard. Visual FoxPro
data types map to SQL Server data types as follows:

                  Visual FoxPro   SQL Server
   Abbreviation   Data Type       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
   N              Numeric         float
   P              Picture         image

Expressions that Map Directly from Visual FoxPro to SQL Server
--------------------------------------------------------------

The following expressions are the same on Visual FoxPro and on SQL Server:

   CEILING()    MAX()     PADR()
   LEN()        MIN()     PROPER()
   LOG()        PADL()    STR()

Index Type Mapping
------------------

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:

   Visual FoxPro Index Type   SQL Server 4.x Index Type
   ----------------------------------------------------
   Primary                    Clustered Unique
   Candidate                  Unique
   Unique, Regular            Non-unique

The 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 underscore (_) character.

NOTE: SQL Server doesn't support ascending or descending indexes, nor does it
permit expressions within server indexes. The Upsizing Wizard removes Visual
FoxPro expressions from index expressions as the index is upsized; only field
names are sent to the server.

SQL Server Data Types that Force Timestamp Columns
--------------------------------------------------

If you choose Add Timestamp Field, by default the Upsizing Wizard adds a new
column with the Timestamp data type if the SQL Server table to be created will
have any of the following data types:

   binary   varbinary   float
   real     image       text

Object Mapping
--------------

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      Update, Insert, and Delete triggers
   (where used for referential
   integrity constraints)

Referential Integrity and Rule Mapping
--------------------------------------

The following table describes the triggers created by the Upsizing Wizard. Any
specific trigger might contain code to emulate some or all of the Visual FoxPro
functionalities 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    Referential integrity
   table only)

Additional query words: VFoxWin

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300
Version           : 3.00

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.