Visual FoxPro Upsizing Wizard

If you're in the unfortunate position that I recently found myself in and need to move data from a Visual FoxPro (VFP for the die-hards) database to a SQL Server database there are a few gotchas you might run into including:

1. The lack of a .dbc file required by the Visual FoxPro Upsizing Wizard.
2. The error: The Upsizing Wizard could not set the SQL Server database to proper compatibility level for upsizing. In order to upsize, Visual FoxPro must set the compatibility level of the target SQL database to 6.5.

1. Creating a Visual FoxPro Database From Pre-existing .dbf Files
If you know anything about VFP then number 1 is probably a no brainer. When I started my project I knew absolutely nothing about VFP (and I still don't). So for those of you that are like me, it's very easy to create the .dbc file. Follow the steps below.

1. Open VFP
2. Click File -> New, select the Database radio button and then click the New File button.
3. When presented with the Save File dialog choose the location for your .dbc file.
4. Now click the Add Table button found in the Database Designer toolbox. 
5. When presented with the Open File dialog, navigate to the directory with your .dbf files and select all the files that you want to add to to your new database (.dbc file).
6. Done. Now you have a database that the Upsizing Wizard can consume.

2. Resolving the 6.5 Compatibility Level Error
Most versions of SQL Server allow you to set the compatibility level of each database within an instance. This allows SQL Server instances of a higher version to host databases of a lower version. For example, SQL Server 2012 allows you to host databases at compatibility level 90, 100, and 110. These numbers are just a confusing way to refer to SQL Server 2005, 2008 (2008 R2 doesn't have it's own compatibility level), and 2012. So what in pray tell is compatibility level 6.5 (65)? It actually refers to SQL Server 6.5 (the one before 7.0 which is the one before 2000). Yeah, it's old. 

Anyway, the VFP Upsizing Wizard requires that the SQL Server that you're upsizing (importing) to be able to support compatibility level 65 (version 6.5). If you're running into this error you're most likely running SQL Server 2008 or above as these versions no longer support compatibility level 65 or 70 and 2012 no longer supports 80 (version 2000).

What is one to do? What I found was the easiest route was to download SQL Server 2005 Express Edition. It's free, it's a small download (~55mb), and it supports 6.5. 

Once you've downloaded and installed SQL Server 2005 Express make sure you create your DSNs (the ones that you'll use in the Upsizing Wizard) using the SQL Server Native Client driver (not SQL Server Native Client 10.0 or 11.0 as these refer to 2008 and 2012).

When creating your DSNs keep in mind that if you're running a 64-bit operating system you must use the 32-bit ODBC Data Source Administrator. THIS DOES NOT MEAN TYPING ODBCAD32.EXE IN THE RUN PROMPT. In order to launch the appropriate administrator you must run it from a directory similar to C:\Windows\SysWOW64\odbcad32.exe.

At this point you should be all set to run the Upsizing Wizard. Good luck!